Query regarding the parameterization of a text file in the SQL

k.kutty · #1 (**permalink**) 06-04-09, 04:26

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

stolze · #2 (**permalink**) 06-04-09, 07:36

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.)

k.kutty · #3 (**permalink**) 06-04-09, 08:46

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 .

stolze · #4 (**permalink**) 06-04-09, 13:37

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

k.kutty · #5 (**permalink**) 06-05-09, 02:51

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

stolze · #6 (**permalink**) 06-05-09, 04:04

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.

k.kutty · #7 (**permalink**) 06-07-09, 10:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech