Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: adding time date stamp to exported files?

    I wrote a script that exports some tables in DEL format and want to append a time/date stamp to each file so that I can tell when the file finished exporting the data. Example:

    from script:

    db2 "export to /stage1/mydb/export/table1.del of DEL MESSAGES export.txt SELECT * FROM db2inst1.table1";

    cd /stage1/mydb/export/
    ls -ls

    table1_092203_1300.del
    where 092203 is the MMDDYY and 130000 is the time HHMMSS


    Any tips on how to do this in a shell script for table exports? Thanks!

    Scott

    PS: Many thanks to the unix programmer gurus who helped me earlier with the FTP and EMAIL section of scripting.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: adding time date stamp to exported files?

    Here you go


    export to triggers_`date +"%m%d%y_%H%M"` of del select trigname from syscat.triggers
    SQL3104N The Export utility is beginning to export data to file
    "triggers_092203_2208".


    HTH

    Sathyaram


    Originally posted by mixxalot
    I wrote a script that exports some tables in DEL format and want to append a time/date stamp to each file so that I can tell when the file finished exporting the data. Example:

    from script:

    db2 "export to /stage1/mydb/export/table1.del of DEL MESSAGES export.txt SELECT * FROM db2inst1.table1";

    cd /stage1/mydb/export/
    ls -ls

    table1_092203_1300.del
    where 092203 is the MMDDYY and 130000 is the time HHMMSS


    Any tips on how to do this in a shell script for table exports? Thanks!

    Scott

    PS: Many thanks to the unix programmer gurus who helped me earlier with the FTP and EMAIL section of scripting.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Posts
    369

    update on timestamp to export/ftp script

    Thanks S. Here is what I have thus far:

    #!/bin/ksh

    # Export database tables
    db2 "EXPORT TO /stage1/table.del OF DEL MESSAGES export.txt SELECT * FROM table";
    #
    #
    # add timestamp to exported file
    db2 "EXPORT to triggers_`date +"%m%d%y_%H%M"` of DEL SELECT trigname FROM syscat.triggers";
    #
    # ftp section
    HOST='MYDBSERVER.COM';
    USER='db2inst1';
    PASSWD='xxx';
    #
    #
    FILES='/stage1/table.del';
    #
    #
    for FILE in $FILES
    do
    ftp -n $HOST <<END_SCRIPT
    quote USER $USER
    quote PASS $PASSWD
    prompt
    cd /stage1/
    put $FILE
    quit
    END_SCRIPT
    echo "Processed " $FILE
    done
    exit 0

    When I run ls on the files I see:
    triggers_092203_1430
    table.del

    I appreciate your help as a DBA scripting is a lost art I need to get back into. Originally told I was production DBA but getting stuck writing a lot of homegrown code and scripts.

  4. #4
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Re: adding time date stamp to exported files?

    Try This

    ----------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------
    //For list of tables where names are stored in a file
    db2 connect to database
    db2 set schema schemaname
    DATE1=`date +"%m%d%y"`
    DATE2=`date +"%H%M"`

    TABLE_LIST=`cat tables.lst`

    for TABLE in ${TABLE_LIST}
    do

    echo "export to ${TABLE}_${DATE1}_${DATE2}.exp of del select * from ${TABLE}"


    done



    ---------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------
    //Just For one Table
    db2 connect to database
    db2 set schema schemaname
    DATE1=`date +"%m%d%y"`
    DATE2=`date +"%H%M"`
    db2 "export to TABLE1_${DATE1}_${DATE2}.exp of del select * from TABLE1"

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


    Originally posted by mixxalot
    I wrote a script that exports some tables in DEL format and want to append a time/date stamp to each file so that I can tell when the file finished exporting the data. Example:

    from script:

    db2 "export to /stage1/mydb/export/table1.del of DEL MESSAGES export.txt SELECT * FROM db2inst1.table1";

    cd /stage1/mydb/export/
    ls -ls

    table1_092203_1300.del
    where 092203 is the MMDDYY and 130000 is the time HHMMSS


    Any tips on how to do this in a shell script for table exports? Thanks!

    Scott

    PS: Many thanks to the unix programmer gurus who helped me earlier with the FTP and EMAIL section of scripting.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: adding time date stamp to exported files?

    Scott, just read your post again and realized that you wanted the end timestamp and not the start timestamp of the export ...

    In this case,

    tabname="Table1"

    db2 "export to $tabname.del of del select * from $tabname"
    mv $tabname.del $tabname_`date +"%m%d%y_%H%M"`.del

    should give the timestamp with very little latency ...


    HTH
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    May 2003
    Posts
    369

    thanks need more feedback the script

    I was able to add a timestamp to the exported files on System A.

    However now that I have a timestamp I need to get these files over to System B via FTP. Any tips on how to do this tricky move? What I have thus far:

    tabname1=CLIENTS

    db2 "EXPORT TO /export/$tabname1.del OF DEL MESSAGES export.txt SELECT * FROM $tabname1";

    mv /export/$tabname1.del /export/${tabname1}_`date +"%m%d%y_%H%M"`.del';

    HOST='SYSTEMB.COM';
    USER='db2inst1';
    PASSWD='xxx';

    # FTP Section to send files that were exported from System A to
    # System B via FTP transfer
    for FILE in $FILES
    do
    ftp -n $HOST <<END_SCRIPT
    quote USER $USER
    quote PASS $PASSWD
    prompt
    cd /export
    put $FILE
    quit
    END_SCRIPT
    echo "Processed " $FILE
    TIME=`date +%D_%T`
    done


    I am able to export the files with the timestamp but when I attempt to ftp them over to System B it fails. Thanks! By the way once I get this all finished I will post on the board for everyone.

Posting Permissions

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