shell script to extract rows from table

[regnumber]

Hi.

I am new to Unix. I need to write a Unix Shell Script to extract records from the table and write those extracted datas to a text file.

DB using is DB2.

Can anyone give me some sample shell script

thanks
Krishnakanth

[spider007]

Is there any specific reason that you want a shell script. You can do a export of the table data using EXPORT command.

[sakumar9]

You can call EXPORT command from a shell script, EXPORT will write the table data into files.

[docdiesel]

Hi,

db2 "select a,b,c from schema.mytable" >file.txt

should do, if you don't need the data in CSV format. Use "db2 -x" if you don't want the column heads.

Regards

Doc Diesel

[regnumber]

Thanks for your reply. Now I have got another issue.
_________________________
echo "Run SQL select current timestamp from SYSIBM.SYSDUMMY1 with ur" >>$monlog
db2 "select current timestamp from SYSIBM.SYSDUMMY1 with ur" >> currenttimestamp
cat currenttimestamp
_________________________

The output what I am getting is as follows:

1
---------------------------------------
2008-10-03-02-00.00.00.000000

1 row(s) selected
=================================

I want only the timestamp to be displayed. which I need to store the timestamp to a variable called currtimestamp.

and I need to pass this currtimestamp to another query as follows.
___________________
db2 "SELECT * FROM BMWDEV1.ZB_RCBL_ERROR WHERE TIMESTAMP_UPDATED >= '2008-10-01-00.00.00.000000' AND TIMESTAMP_UPDATED > currtimestamp WITH UR" >>$monlog
___________________

Can anyone help me out to fix this issue.

Thanks in advance.

Krishnakanth

[docdiesel]

Hi,

either you use "db2 -x SELECT ..." to suppress the header & footer, or you grep for the leading digit "2" (of 2008...):

db2 "SELECT ... " | grep ^2 >file.txt

Instead of that you may store the date in a shell variable and use the latter in the next step:

DTSTAMP=` db2 -x "select ..." `
db2 "select * ... where WHERE TIMESTAMP_UPDATED > '$DTSTAMP' "
 

I guess you need the timestamp of the beginning of the script for more than one action. I'd suggest to create an auxiliary table {var varchar(20), value varchar(128)}, for script internal values:

insert into my.variables (var,value)
  SELECT 'tstamp', Current_timestamp
  from sysibm.sysdummy1 ;
 
SELECT * from ...
WHERE TIMESTAMP_UPDATED>(
  timestamp(select value from my.variables where var='tstamp')
) ;
 

Regards

Doc Diesel