Question about EXPORT

tbelian · #1 (**permalink**) 06-08-09, 13:32

Is there a way to dynamically construct the filename used for the EXPORT statement? Instead of using the same file name each time I run an export, I want to add (append) a date/time to the file name.

My current statement looks something like this:

export to myfile.csv of del modified by coldel, decpt. select * from mytable

So, instead of the file being named 'myfile.csv' I would like to construct a name like

'myfile06082009133300.csv'

Any help would be appreciated.

Cougar8000 · #2 (**permalink**) 06-08-09, 14:12

yes, write a shell script and use variables

todaydt=`date +%Y%m%d`

export to tabname$todaydt.csv .....

tbelian · #3 (**permalink**) 06-08-09, 14:47

If you mean a linux/unix shell, I'm not knowledgeable about those, sorry.

Stealth_DBA · #4 (**permalink**) 06-08-09, 16:01

tbelian, If you don't want to write linux shell scripts, you can try creating the Export commands with a Select statement like this:

Code:

SELECT 'export to ' || NAME 
    || '.csv of del modified by coldel, decpt. select * from ' 
    || CREATOR || '.' || NAME
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
  AND NAME = 'mytable'

I am only selecting 'MYTABLE' but you can select how ever many tables you like (for example AND CREATOR = 'schema-name').

Send the results to a file then execute the file from the command line.

PS I just threw this together, it may need a little tweaking to get the output correct.

tbelian · #5 (**permalink**) 06-09-09, 13:19

Thanks for the hints and suggestions.

I'm 'almost' there (where I want to be).

Taking Stealth's approach I have 3 files involved.

File #1 (the .bat file) looks like this:

db2cmd db2 -txf step1.sql -r callextract.sql
db2cmd /C db2 -f callextract.sql
exit

File #2 (the step1.sql file) looks like this:

connect to cti_data user abcdefg using qbcdefgx;
SELECT 'export to ' || NAME || REPLACE(CHAR(current date,iso),'-','') || '.csv of del modified by coldel, decpt. select * from ' || RTRIM(CREATOR) || '.' || NAME FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND NAME = 'CTI_CALLLOG_TODAY';
TERMINATE;
EXIT;

File #3 becomes the roadblock. I have the resultant 'connection info' as part of the output:

Database Connection Information

Database server = DB2/NT 9.1.2
SQL authorization ID = CALLVIEW...
Local database alias = CTI_DATA

export to CTI_CALLLOG_TODAY20090609.csv of del modified by coldel, decpt.

Any additional suggestion on how to supress the connection information? Quiet Mode?

Thanks

Stealth_DBA · #6 (**permalink**) 06-09-09, 13:33

Depending on what you are doing, just make the connection to DB2 on the Command line first and then run the other two files. The connection has already been established so they should process without problems.

However, if you are trying to set up an automated process, this is more difficult. There are methods but I am not expert enough to describe them.

tbelian · #7 (**permalink**) 06-09-09, 13:36

Thanks again for the response.

Yes, I'm trying to set up an automated process, which is the challenge. I need to try and incorporate it all into the single .bat file, consequently the connect is included in the file passed in the first call to db2cmd.

I'll keep hunting. I am spending plenty of time here:

DB2 Universal Database

Thanks again.

DBFinder · #8 (**permalink**) 06-10-09, 07:35

tbelian,

I can provide you a customized script, if you still have any problem.
Windows NT no problem.

Let me know if you need.

Thanks
DBFinder

tbelian · #9 (**permalink**) 06-10-09, 07:43

DBFinder,

Sure, if you wouldn't mind. Let me know what information you need.

Thanks

DBFinder · #10 (**permalink**) 06-10-09, 08:06

All right,

I will help you write your own, OR I will provide you one.

I will post back in an hour and half. Leaving for work.

DBFinder

DBFinder · #11 (**permalink**) 06-10-09, 09:36

Hi tbelian,

Following batch file should be good for Win 2k and win 2k3, I guess win nt 4.0 too.

Name it as anybatchfile.bat and run it in DB2 command Window
Please note that there are no semicolons at the end of commands preceeded by keyword db2.

Code:

@echo off

db2 connect to cti_data user abcdefg using qbcdefgx

::=======Date Stamp :: To be used as %DTSTMP% in following Script

for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do set "YY=%%l"
for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do set "MM=%%j"
for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do set "DD=%%k"
Set "DTSTMP=%YY%%MM%%DD%"


db2 export to myfile%DTSTMP%.csv of del modified by coldel, decpt. select * from mytable


set "YY="
set "MM="
set "DD="
Set "DTSTMP="

Please Let me know if this does not work.

DBFinder

tbelian · #12 (**permalink**) 06-10-09, 12:20

Thanks, I will give it a try.

tbelian · #13 (**permalink**) 06-11-09, 08:22

Thanks for the script. As described, it works fine. "But" ... the problem is that it needs to be launched from within the db2 command window.

As a stand alone batch file, launched via scheduled tasks in Windows, a roadblock pops up. At that time the BAT file is not running in a db2 command window. So I'm kinda stuck at that point.

DBFinder · #14 (**permalink**) 06-11-09, 09:34

Not a problem.

In windows scheduler use this command

Quote:

db2cmd.exe -c -w <path>anybatchfile.bat

when you will save the task properties window this will be expanded as

Quote:

C:\PROGRA~1\IBM\SQLLIB\BIN\db2cmd.exe -c -w <path>anybatchfile.bat

Please note that windows will expand the program name to fully qualified. For batch file you will provide fully qualified.

Remember to mention working directory in 'Start in' box. Otherwise your output files will be lost.

Anything else ?? Let me know.

Good Luck
DBFinder

n_i · #15 (**permalink**) 06-11-09, 09:43

Quote:

Originally Posted by tbelian

As a stand alone batch file, launched via scheduled tasks in Windows, a roadblock pops up. At that time the BAT file is not running in a db2 command window.

Not sure what you mean by the popping-up roadblock, but you may be able to find a solution in this thread: dos scripits for db2 - dBforums