Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2009
    Posts
    7

    Unanswered: Question about EXPORT

    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.

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    yes, write a shell script and use variables

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

    export to tabname$todaydt.csv .....
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Jun 2009
    Posts
    7
    If you mean a linux/unix shell, I'm not knowledgeable about those, sorry.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2009
    Posts
    7

    Almost there

    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

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jun 2009
    Posts
    7
    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.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  9. #9
    Join Date
    Jun 2009
    Posts
    7
    DBFinder,

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

    Thanks

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  12. #12
    Join Date
    Jun 2009
    Posts
    7
    Thanks, I will give it a try.

  13. #13
    Join Date
    Jun 2009
    Posts
    7
    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.

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Not a problem.

    In windows scheduler use this command

    db2cmd.exe -c -w <path>anybatchfile.bat
    when you will save the task properties window this will be expanded as

    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

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •