Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014

    Question Unanswered: problems automating batch

    I'm puzzled and have spent the last two hours on Google and modifying my script and just can't get any satisfaction. CLP indicates I'm running DB2 Client 10.1.2 I'm in an enterprise environment calling a server in a server farm at another location than my computer. My desktop system is Windows 7. The DB2 server is LUW, I assume version 10.?

    So If I open the Windows command line processor and enter db2cmd that opens the DB2 CLP.
    In the DB2 CLP window I enter DB2 and get about ten lines of messages and a command prompt db2 =>
    I then paste the following into the command line window.
    connect to ABCDEF_P USER myusername USING mypassword
    EXPORT TO H:\IncomingData\tblHistFacName.txt OF DEL MODIFIED BY COLDEL| NOCHARDEL DECPLUSBLANK STRIPLZEROS MESSAGES H:\Incomingdata\output\tblHistFacNameMessages.txt SELECT mycolumnlist FROM ABCD."tblHistFacName" 
    EXPORT TO H:\IncomingData\tblAbuse.txt OF DEL MODIFIED BY COLDEL| NOCHARDEL DECPLUSBLANK STRIPLZEROS MESSAGES H:\Incomingdata\output\tblAbuseMessages.txt SELECT mycolumnlist FROM ABCD."tblAbuse" 
    EXPORT TO H:\IncomingData\tblFacility.txt OF DEL MODIFIED BY COLDEL| NOCHARDEL DECPLUSBLANK STRIPLZEROS MESSAGES H:\Incomingdata\output\tblFacilityMessages.txt SELECT mycolumnlist FROM ABCD."tblFacility"
    I get my three data files in H:\IncomingData and I get my three message files. All is well. Problem is I can't do that at 3:00 AM daily on a server, nor can I do it on vacation or after being hit by a bus. So this needs to be automated and that's where I start having issues.

    So step one I've created a Windows batch file to trigger the file containing the code I pasted in below. That trigger file contains this one line.
    db2cmd H:\Scripts\EXPORT_ABCD_extract.bat
    When I fire this I immediately get an error that CONNECT and EXPORT are unknown commands. Using various Google searches I concluded that I needed to put db2 before each CONNECT and EXPORT line. That ended that error.

    Next error hits on option after the pipe character.
    EXPORT TO H:\IncomingData\tblHistFacName.txt OF DEL MODIFIED BY COLDEL|
    Any word inserted after the pipe character errors out as an unknown command, and I've tried running with various combinations right down to no options, just the SELECT and everyone throws the unknown command error, including SELECT!
    I have tried placing a lone db2 at the top of the batch file (instead of before the CONNECT and EXPORT commands) (failed).
    I've tried repeating db2 after the pipe character (failed).

    In scripting against Oracle, Windows and SQL Server I've done before I've never seen a case where scripting that works pasted manually fails once automated. I'm totally frustrated and I'm not finding a solution using Google or trying the search at boulder.

    I'm figuring it's a simple problem either something is missing in my trigger batch or at the beginning of my primary batch. Any help would be appreciated.

    I would prefer not to run with this many options, just seems to be what works best with my data and quit a few days of trial and error getting this to work manually running it in the command line processor.
    The emoticon list needs one banging it's head on a desk.


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'd probably try to rename H:\Scripts\EXPORT_ABCD_extract.bat as H:\Scripts\EXPORT_ABCD_extract.sql then use the command:
    db2cmd -f"H:\Scripts\EXPORT_ABCD_extract.sql"
    The "-f" option would be my first choice, and the quotation marks allow path and file names with spaces, etc. if those should occur.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2003
    You are using case-sensitive table names? OMG!!
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2008
    Iasi, Romania
    Provided Answers: 2
    Let me show you how I do it. Hope it will help you.

    4. I create a .sql file where I put all the statements, ended with @, like:
    EXPORT to myfile ... etc ... @

    3. I create a bat file named let's say mainbat.bat where I have:
    db2 connect to %1 user %2 using %3
    db2 -v -td@ -f mySQLfile.sql
    db2 COMMIT
    db2 terminate

    2. I have another bat file that loads DB2 CLP, named loadDB2.bat:
    db2cmd -c -w -i %1 %2 %3 %4

    1. I create a bat file, let's say startbatch.bat
    call loadDB2 mainbat.bat servername username password

    Now you can add startbatch.bat as a Windows scheduled task.

    I've found this solution here:
    Last edited by aflorin27; 03-27-14 at 03:44.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Mar 2014
    I recreated the H:\ path in my C:\ drive and it works. Only change H to C and of course the directories. The running theory is DB2 is using some network protocol to write the files that our IT security people have intentionally or unintentionally blocked. The good news when this is scheduled on the server H: is a physical drive on the server not a network drive like it is on my system. I will try the various argument options presented here to see if that helps resolve the issue.

    As far as table and column naming it gets worse. The OLTP database predates my working for this agency. It was originally an Access database (front end still is). Pretty much every rule you can name for naming have been violated in this thing (spaces and reserved words so when running a DDL every entity in the table is wrapped in full quotes). My job is to move data into a data warehouse for reporting purposes. As I get into transforming this thing better naming rules will be applied.

    Thanks for the help.


Tags for this Thread

Posting Permissions

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