Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Post Unanswered: Convert MSSQL to DB2 then run from DOS & UNIX shell

    Hello,

    I'm a newbe to DB2 and I'm pretty lost. I am trying to take some existing MSSQL queries that are run at the command line (on Win & Unix) work for DB2.

    1. I first connect to DB2:

    db2 connect to indexdb user f_maint using <PASS>

    2. How do I run an SQL query from the command line? I've done some searching, but I'm not sure if this is correct:


    db2 -tvf myQuery.sql

    2. What should the contents be of myQuery.sql? Here is a sample one for MSSQL, but it does NOT work. When I try to run it, I get this message:

    C:\Program Files\IBM\SQLLIB\BIN>db2 -tvf c:\temp\myQuery.sql

    DB21007E End of file reached while reading the command.


    Contents of myQuery.sql:


    SELECT DOCTABA.F_DOCNUMBER
    FROM F_SW.DOCTABA AS DOCTABA, F_SW.DOCTABA AS DOCTABA_1
    WHERE DOCTABA.F_DOCNUMBER > 100000 AND DOCTABA_1.F_DELETEDATE IS NOT NULL AND DOCTABA.F_RETENTBASE = '1' AND DOCTABA.F_DELETEDATE = 9443 OR DOCTABA.F_ARCHIVEDATE IS NOT NULL AND DOCTABA.F_DELETEDATE IS NULL OR DOCTABA.F_ARCHIVEDATE IS NOT NULL AND DOCTABA.F_RETENTBASE = '1' AND DOCTABA.F_ARCHIVEDATE >= 9443
    ORDER BY DOCTABA.F_DOCNUMBER ASC


    I built the query in SQL Assist (DB2 tool).




    Any help would be greatly appreciated. Thanks

    Chris

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

    Re: Convert MSSQL to DB2 then run from DOS & UNIX shell

    A semicoln at the end of the sql statement ...

    SELECT DOCTABA.F_DOCNUMBER
    FROM F_SW.DOCTABA AS DOCTABA, F_SW.DOCTABA AS DOCTABA_1
    WHERE DOCTABA.F_DOCNUMBER > 100000 AND DOCTABA_1.F_DELETEDATE IS NOT NULL AND DOCTABA.F_RETENTBASE = '1' AND DOCTABA.F_DELETEDATE = 9443 OR DOCTABA.F_ARCHIVEDATE IS NOT NULL AND DOCTABA.F_DELETEDATE IS NULL OR DOCTABA.F_ARCHIVEDATE IS NOT NULL AND DOCTABA.F_RETENTBASE = '1' AND DOCTABA.F_ARCHIVEDATE >= 9443
    ORDER BY DOCTABA.F_DOCNUMBER ASC ;
    Visit the new-look IDUG Website , register to gain access to the excellent content.

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

    Re: Convert MSSQL to DB2 then run from DOS & UNIX shell

    BTW, you can check error message descriptions from the command line

    db2 ? <message-code>

    db2 ? DB21007E
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2004
    Posts
    5
    Thank you for all your help. Both of your responses have been very helpful.


    Can you tell me if there is anything that I could modify for this MSSQL query to make it work for db2?


    -- ms-sql server 7.0
    -- q2.sql.syb

    PRINT "Query2: F_DOCNUMBER > 100200; ; 300 ; ;"
    USE indexdb
    SETUSER 'f_sw'
    GO

    DECLARE csr CURSOR FOR
    SELECT f_docnumber FROM doctaba
    WHERE (f_docnumber > 100200) AND
    ((f_deletedate is not null and f_retentbase='1' and f_deletedate >= 9443) OR
    (f_archivedate is null and f_deletedate is null) OR
    (f_archivedate is not null and f_retentbase = '1' and f_archivedate >= 9443))
    ORDER BY f_docnumber
    GO

    OPEN csr
    FETCH csr
    DEALLOCATE csr
    GO;

Posting Permissions

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