KSH function handy tool for SQL

[bmcferon]

I realize most of you AIX pros know how to do this already, but I will post this up for anyone who might need it.



This function will strip out all the session connection information when issueing a batch command from a KSH script for TSM. it strips both the initial connection information, AND the disconnection (and return codes) out of whatever you are doing......the basic result is raw data.



function tsmchomp {

sed 1,12d | sed -e :a -e '$d;N;2,4ba' -e 'P;D'

}



This is for select statements , if you modify the first sed comment, you can use it for built-in TSM querys and commands as well.



now if someone wants to be really nice, they will print up the same function in PERL and I would be grateful =)





Brandon

 

[Sniper]

Please, describe what does this garbage do and i'll translate it in Perl for you



it delete the first 12 lines and ...





David "Sniper" Rigaudiere

 

[itdrew]

It looks to me like the tsmfunction tsmchomp in the below example will take output from a comman line like this:

----------------------------------------------------





tsmserv1:/root>dsmadmc -id=user -password=password q occ sqldb

Tivoli Storage Manager

Command Line Administrative Interface - Version 5, Release 1, Level 5.0

(C) Copyright IBM Corporation 1990, 2002 All Rights Reserved.



Session established with server ADSM: AIX-RS/6000

Server Version 5, Release 1, Level 6.2

Server date/time: 10/01/03 14:41:21 Last access: 10/01/03 14:29:51



ANS8000I Server command: 'q occ sqldb'



Node Name Type Filespace FSID Storage Number of Physical Logical

Name Pool Name Files Space Space

Occupied Occupied

(MB) (MB)

---------- ---- ---------- ----- ---------- --------- --------- ---------

sqldb Bkup \0004232- 3 DISKPOOL1 134 0.15 0.15

846f7c$

sqldb Bkup \0004232- 3 LTOPOOL 51,265 5,929.57 5,898.97

846f7c$

sqldb Bkup \0004232- 3 OFFSITE 51,265 5,930.56 5,898.97

846f7c$

sqldb Bkup SYSTEM 4 DISKPOOL1 1,603 211.17 211.17

OBJECT

sqldb Bkup SYSTEM 4 LTOPOOL 30,449 4,004.30 4,004.30

OBJECT

sqldb Bkup SYSTEM 4 OFFSITE 30,449 4,004.30 4,004.30

OBJECT



ANS8002I Highest return code was 0.





----------------------------------------------------



and turn it into this:



----------------------------------------------------





---------- ---- ---------- ----- ---------- --------- --------- ---------

sqldb Bkup \0004232- 3 DISKPOOL1 134 0.15 0.15

846f7c$

sqldb Bkup \0004232- 3 LTOPOOL 51,265 5,929.57 5,898.97

846f7c$

sqldb Bkup \0004232- 3 OFFSITE 51,265 5,930.56 5,898.97

846f7c$

sqldb Bkup SYSTEM 4 DISKPOOL1 1,603 211.17 211.17

OBJECT

sqldb Bkup SYSTEM 4 LTOPOOL 30,449 4,004.30 4,004.30

OBJECT

sqldb Bkup SYSTEM 4 OFFSITE 30,449 4,004.30 4,004.30

OBJECT



without the header info.



So Brandon wants to know how to do it in perl.

 

[bmcferon]

basically what Drew says:



Takes batch session , and chomps off the header/footer so we just get the raw data.



In my case, we work from the assumption that we are -tabdelimited on the batch command.



and it kills I believe the first 12 lines

and the last 4 lines of the output





the first 12 lines woudl be easy to convert in PERL......its chopping the last 4 lines, that is the tricky part.....since we never know how many lines the raw data is going to be.

 

[Sniper]

Ok, but does it store all the result of dsmadmc command in memory before cut off

the last 4 lines ?



if yes :



<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><font class="pn-sub">Code:</font><HR></TD></TR><TR><TD><FONT class="pn-sub"><PRE>

my @buffer = qx(dsmadmc -id=... -password=... -tabd command);

splice(@buffer,0, ;

splice(@buffer,-3, 3);

</PRE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE>



I use this piece of code.

or sometimes this one :



<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><font class="pn-sub">Code:</font><HR></TD></TR><TR><TD><FONT class="pn-sub"><PRE>

dsmadmc ... command | perl -ne 'print if /^ANS8000I/ .. /^ANS8002I/'

</PRE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE>



David "Sniper" Rigaudiere

 

[bmcferon]

In the way ive got it setup in KSH, Yes, it does.



Does it HAVE to be that way? no...you could dump to a temp file and do a line count. I dont like to code that way unless I have to.





Brandon