Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    9

    Unanswered: Query regarding the parameterization of a text file in the SQL

    Hi,


    From the below mentioned SQL I have to read all the codes
    from a text file.The file contains values like
    'AA'
    'BB'
    'C0'
    ...etc.

    I have created thie text file in my unix box and I have to pass the file name as a parameter while executing my application.But ,during the run time it shows the below mentioned error message. Could you please suggest a solution for this.

    MESSAGE: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "GROUP" was found following "ting_carr_code.txt')". Expected tokens may include: ")". SQLSTATE=42601
    Last edited by k.kutty; 06-07-09 at 11:24.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I formatted the query a bit so that it becomes easier readable and maintanable:
    Code:
    SELECT al1.orig_arpt_id, al1.dest_arpt_id, al1.mkt_carr_cd, al1.mkt_flt_nbr, al1.oper_flt_nbr, al1.oper_carr_cd, al3.db2_dt, MIN ( al1.ctl_id ) AS ctl_id_min
    FROM   ewh.mkt_flt_leg_sched_hist al1, ewh.dt_conv al3
    WHERE  ( al1.mkt_carr_cd <> al1.oper_carr_cd ) AND
           ( al1.mkt_leg_eff_dt <= al3.db2_dt AND
             al1.mkt_leg_term_dt >= al3.db2_dt ) AND
           ( ( NOT LOCATE(RTRIM(CHAR(al3.wk_da_nbr)), al1.mkt_oper_wkda, 1) =  0) AND
             ( CHAR(YEAR(al3.db2_dt)) || CHAR(MONTH(al3.db2_dt)) BETWEEN
                  CHAR(YEAR(DATE(TIMESTAMP_FORMAT('200904','YYYYMM') ) - 2 MONTHS)) || CHAR(MONTH(DATE(TIMESTAMP_FORMAT('200904','YYYYMM' )) - 2 MONTHS)) AND
                  CHAR(YEAR(DATE(TIMESTAMP_FORMAT('200904','YYYYMM') ) - 2 MONTHS)) || CHAR(MONTH(DATE(TIMESTAMP_FORMAT('200904','YYYYM M')) - 2 MONTHS))) AND
               al1.mkt_carr_cd IN ('/devl/apps/edw/dwp_ap00/dwpav/bin/marketing_carr_code.txt') AND
               al1.oper_carr_cd IN ('/devl/apps/edw/dwp_ap00/dwpav/bin/marketing_carr_code.txt')
    GROUP BY al1.orig_arpt_id, al1.dest_arpt_id, al1.mkt_carr_cd, al1.mkt_flt_nbr, al1.oper_flt_nbr, al1.oper_carr_cd, al3.db2_dt
    There is a closing parenthesis ')' missing somewhere in the WHERE clause.

    Also, the BETWEEN is rather complicated. Why don't you use a simple "BETWEEN DATE('2009-04-01') - 2 MONTHS AND DATE('2009-04-01') - 2 MONTHS"? (I am not sure what the BETWEEN should really do. The only difference is an additional space between the two 'M's, which may not be intentional.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2009
    Posts
    9
    Hi,

    Thanks for your suggestion.I added one more paranthesis on the right side.Now,while executing the job ,my application is unloading the table with zero records and continue with the other steps.But,the SQL is not reading the contents from the text file
    /devl/apps/edw/dwp_ap00/dwpav/bin/marketing_carr_code.txt which I have specified for MKT_CARR_CD and oper_carr_cd.Actually ,this text file contains all the carrier codes as
    'AA',
    'B0',
    'C1',
    'D2'

    and I have to pass this text file as a parameter at run time.

    If anybody knows please help me to resolve this .

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A construct like this:
    Code:
    AL1.OPER_CARR_CD IN ('/devl/apps/edw/dwp_ap00/dwpav/bin/marketing_carr_code.txt')
    tests whether the current value in column OPER_CARR_CD is equal to the string. It does not interpret the string as file name and does not try to open and read the file. You have to use a table function for that. An example is described here: Making Operating System Calls from SQL
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2009
    Posts
    9
    Hi Stolze,

    Thanks for your suggestion.I read the article which you provided me in the URL.It is very much informative.But my application is running on Unix platform .I am working in ETL tool Abinitio.So I cant execute C code files in that.Since I am a biggner in Db2 and shell script ,I created a text file and then passed it .I think its better to go for a shell script ,so that i can pass the script name as a parameter.But ,still i am not sure whether the problem will get resolved with that.

    Thanks

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The problem is that you have to get the values out of your file into the SQL statement. You have 3 options for that:
    - you extract the data in some application (or shell script) from the file and then build the SQL statement based on that data
    - you invoke some function like the C UDF I mentioned or a Java UDF to read the file dynamically at run time
    - you use federation and represent the file as nickname (I am not sure if that would work with dynamic file names, but you could handle this via symlinks)

    The argument that you can't execute C code is not an issue. As the article describes, you compile and link the code on a development system so that you get a shared library. On the production system, you copy the library to sqllib/function/ and execute a CREATE FUNCTION statement in the DB2 database. No compiler/linker is needed on the production system.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jun 2009
    Posts
    9
    Hi Stolze,
    Thanks for your information.Could you pls delete the formatted SQL from your first reply.Actually i have some issues in keeping the SQL in the Forums

Posting Permissions

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