Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

processing db2 output

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting processing db2 output
# 1  
Old 04-12-2010
processing db2 output
db2 command produces this output:
Code:
Database 1 entry:
 Database alias                       = DB1
 Database name                        = DB1
 Node name                            = ND1
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =
Database 2 entry:
 Database alias                       = DB2
 Database name                        = DB2
 Node name                            = ND2
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Remote
 Authentication                       = SERVER
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =
Database 3 entry:
 Database alias                       = DB3
 Database name                        = DB3
 Node name                            = ND3
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =
Database 4 entry:
 Database alias                       = DB4
 Database name                        = DB4
 Local database directory             = /home/db4
 Database release level               = a.00
 Comment                              = 
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
Database 5 entry:
 Database alias                       = DB5
 Database name                        = DB5
 Local database directory             = /home/db5
 Database release level               = a.00
 Comment                              = 
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

I need to get "Database name" that has "Directory entry type" as Indirect. From the above output, I need to get only DB4 and DB5.
I have a method that is working, but it is circumvented (using cut, paste & using temporary files). I am looking for simple and elegant way..Thanks
# 2  
Old 04-12-2010
Quote:
Originally Posted by valero
...
I need to get "Database name" that has "Directory entry type" as Indirect. From the above output, I need to get only DB4 and DB5.
...
Assuming that the db2 output is stored in a file called "db2_output", then here's one way of doing it with Perl:

Code:
perl -lne '/^\s*Database name(=| )*(.*?)$/ and $name=$2; print $name if (/^\s*Directory entry type(=| )*Indirect/)' db2_output

Test run:

Code:
$
$
$ cat db2_output
Database 1 entry:
 Database alias                       = DB1
 Database name                        = DB1
 Node name                            = ND1
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =
Database 2 entry:
 Database alias                       = DB2
 Database name                        = DB2
 Node name                            = ND2
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Remote
 Authentication                       = SERVER
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =
Database 3 entry:
 Database alias                       = DB3
 Database name                        = DB3
 Node name                            = ND3
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =
Database 4 entry:
 Database alias                       = DB4
 Database name                        = DB4
 Local database directory             = /home/db4
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
Database 5 entry:
 Database alias                       = DB5
 Database name                        = DB5
 Local database directory             = /home/db5
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
$
$ perl -lne '/^\s*Database name(=| )*(.*?)$/ and $name=$2; print $name if (/^\s*Directory entry type(=| )*Indirect/)' db2_output
DB4
DB5
$
$

Alternatively, if the output of the command is not stored in a file, you could pipe the one-liner to your db2 command thusly -

Code:
<your_db2_command> | perl -lne '/^\s*Database name(=| )*(.*?)$/ and $name=$2; print $name if (/^\s*Directory entry type(=| )*Indirect/)'

HTH,
tyler_durden
# 3  
Old 04-12-2010
thanks!
Thanks a lot durden_tyler!! I don't have a clue how your command works, I should pick up a Perl basics book then Smilie

---------- Post updated at 09:37 PM ---------- Previous update was at 09:35 AM ----------

I would like to re-organize the above db2 output to columns-values format:
Code:
Database alias,Database name,Local database directory,Node name,Database release level,Comment,Directory entry type,Authentication,Catalog database partition number,Alternate server hostname,Alternate server port number
DB1,DB1,,ND1,a.00,,Remote,,-1,,,
DB2,DB2,,ND2,a.00,,Remote,SERVER,-1,,,
DB3,DB3,,ND3,a.00,,Remote,,-1,,,
DB4,DB4,/home/db4,,a.00,,Indirect,,0,,,
DB5,DB5,/home/db5,,a.00,,Indirect,,0,,,

again, I am using "cut-paste-grep-temp files" to get the desired structure...how to do this re-organize in perl...Thanks
# 4  
Old 04-13-2010
Code:
$ 
$ 
$ perl -F= -lane 'chomp; $F[1]=~s/^\s*|\s*$//g;
>                 if (!/^Database/) {
>                   if (! $disp) {
>                     $F[0]=~s/^\s*|\s*$//g;
>                     $hdr .= ",$F[0]";
>                   }
>                   $data .= ",$F[1]";
>                 }
>                 elsif (/^Database/) {
>                   if ($hdr) {
>                     print substr($hdr,1);
>                     $hdr=""; $disp=1;
>                   }
>                   if ($data) {
>                     print substr($data,1);
>                     $data="";
>                   }
>                 }
>                 END {print substr($data,1)}
>                ' db2_output
Database alias,Database name,Node name,Database release level,Comment,Directory entry type,Catalog database partition number,Alternate server hostname,Alternate server port number
DB1,DB1,ND1,a.00,,Remote,-1,,
DB2,DB2,ND2,a.00,,Remote,SERVER,-1,,
DB3,DB3,ND3,a.00,,Remote,-1,,
DB4,DB4,/home/db4,a.00,,Indirect,0,,
DB5,DB5,/home/db5,a.00,,Indirect,0,,
$ 
$

tyler_durden
# 5  
Old 04-13-2010
Works, but...
Thanks durden_tyler, you rock! I am trying to use the your code for another scenario, and it gives me all the output in one line, well, it really shows that I got to learn perl Smilie

Can you suggest me good online source for learning perl syntax, thanks again!!
# 6  
Old 04-13-2010
Quote:
Originally Posted by valero
...Can you suggest me good online source for learning perl syntax...
If you have Perl installed in your machine, then the syntax documentation comes with it. Check out the "perldoc" command.
On some OSes (e.g. Ubuntu), it's a separate installation.

The online sources are just rehashes of the perldoc documentation.

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Processing diff output

How to get diff to not print the chevrons and the dashes? In this case the differences are all single line differences. Also the first few lines don't matter. How to get the output to always exclude the first few lines? Thanks! (1 Reply)
Discussion started by: stevensw
1 Replies

2. UNIX for Dummies Questions & Answers

Take output of processing in text file

Hi ALL, I am presently using perl script mukesh.pl I just want to catch its output into another text file . So I am using > File.txt . I am getting output but i want the whole processing of the script into that file please let me know . Thanks in advance Cheers Mukesh (1 Reply)
Discussion started by: mumakhij
1 Replies

3. Shell Programming and Scripting

Printing the output of a db2 query on to an unix file

I want to print the output of a db2 query, on to an unix file in a manner that the columns are separated by 'commas'. Please help me out..thanx in advance (1 Reply)
Discussion started by: prasan_ven
1 Replies

4. Shell Programming and Scripting

processing the output of AWK

Hi my input file is <so > < Time > <Pid> <some ro><Job Name> 111004 04554447 26817 JOB03275 MBPDVLOI 111004 04554473 26817 JOB03275 MBPDVLOI 111004 04554778 26807 JOB03276 MBPDVAWD 111004 04554779 26807 JOB03276 MBPDVAWD 111004 04554780 26817 ... (4 Replies)
Discussion started by: rakeshkumar
4 Replies

5. Shell Programming and Scripting

Need Fix in Exporting a db2 query output

When I use export for sql query in a Script : Select '1',ModHist',count(*) from cc.mdhist; it was exporting the record to a file as: "1" "ModHist" 778201 Here 1 & ModHist are coming in Doublequotes. If I run the above query directly in Unix prompt it was displaying the output as... (1 Reply)
Discussion started by: karumudi7
1 Replies

6. IP Networking

processing tcpflow output

I'm using tcpflow to analyze traffic traces. When I launch tcpflow with -r option it creates some files, one for each flow. The problem is that some of these files are not readable. I tryed to process them with awk, but also using it i cannot visualize them correctly. Can anyone suggest me a... (5 Replies)
Discussion started by: littleboyblu
5 Replies

7. UNIX for Dummies Questions & Answers

Exception while loading DB2 driver Class.forName("com.ibm.db2.jcc.DB2Driver")

Hi... I m working on UNIX z/OS. Actually i have to pass the parameters from the JCL to java-db2 program thru PARM. I am able to pass the arguments but the problem occured is, it is throwing an exception while loading the db2 driver as 'Javaclassnotfound:com.ibm.db2.jcc.DB2Driver'... (0 Replies)
Discussion started by: Sujatha Gowda
0 Replies

8. Shell Programming and Scripting

running db2 sql and exporting output from ksh scipt

Hi there, I am trying to write a shell script as root on AIX 5.3 where I change user to db2inst1, connect to our db2 database, run a sql select query and export the result of the query to a file. The code I have so far is as follows:- #!/usr/bin/ksh su - db2inst1 -c "db2 connect to... (0 Replies)
Discussion started by: candlino
0 Replies

9. Shell Programming and Scripting

Run SQL queries in DB2 and output to file

Hi, I new to Unix and scripting. Following is my requirement. Can someone tell me whether its possible or not. Also please let me know how to proceed further if this is possible. List of queries are stored in a file. For example, I have to run a query like this: Select * from &XYZ where... (0 Replies)
Discussion started by: simhasuri
0 Replies

10. Shell Programming and Scripting

flags to suppress column output, # of rows selected in db2 sql in UNIX

Hello, I am new to db2 SQL in unix so bear with me while I try to explain the situation. I have a text file that has the contents of the where condition that I am using for a db2 SQL in UNIX ksh. Here is the snippet. if ; then echo "Begin processing VALUEs" ... (1 Reply)
Discussion started by: jerardfjay
1 Replies
Login or Register to Ask a Question