View previous topic :: View next topic |
Author |
Message |
MFdigger Beginner
Joined: 09 Sep 2005 Posts: 124 Topics: 52 Location: Chicago
|
Posted: Thu Apr 16, 2009 12:04 pm Post subject: BIND issuess |
|
|
Hi All,
WHile compiling the program, I'm facing the following issues with the BIND card. I verified the tables
for the fields and also cursor definitions. Could some one please help me out on this.
Code: |
DSNX105I - BIND SQL WARNING
USING TTKL12 AUTHORITY
PLAN=XY10KK
DBRM=XY10KK
STATEMENT=4688
SQLCODE=-206
SQLSTATE=42703
TOKENS=1994-12-31
DSNX105I - BIND SQL WARNING
USING TTKL12 AUTHORITY
PLAN=XY10KK
DBRM=XY10KK
STATEMENT=4844
SQLCODE=-206
SQLSTATE=42703
TOKENS=P
DSNX105I - BIND SQL WARNING
USING TTKL12 AUTHORITY
PLAN=XY10KK
DBRM=XY10KK
STATEMENT=14634
SQLCODE=-206
SQLSTATE=42703
TOKENS=0001-01-01
DSNX200I - BIND SQL ERROR
USING TTKL12 AUTHORITY
PLAN=XY10KK
DBRM=XY10KK
STATEMENT=15022
SQLCODE=-504
SQLSTATE=34000
TOKENS=@ST5041202
CSECT NAME=DSNXEPP
RDS CODE=-130
|
Thank you _________________ Tx
Digger |
|
Back to top |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Thu Apr 16, 2009 12:18 pm Post subject: |
|
|
I would look -206 and -504 up in the manual. There are many thing that could cause the -206, the -504 is related to a cursor. _________________ Thanks,
NASCAR9 |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Thu Apr 16, 2009 12:39 pm Post subject: |
|
|
MFdigger,
By now you should be in position to solve the errors by yourself. Anyway follow these
1. Click on "Quick Manuals " on top of this page
2. Scroll down to DB2 section
3. click on "DB2 V8 Messages and Codes"
4. click on
5. Search for -206 and read upon it and correct your program
6. Search for -504 and read upon it and correct your program
Kolusu |
|
Back to top |
|
|
MFdigger Beginner
Joined: 09 Sep 2005 Posts: 124 Topics: 52 Location: Chicago
|
Posted: Thu Apr 16, 2009 3:33 pm Post subject: |
|
|
Hi Kolusu/Nascar
Thank you for the details. I think I should have provided with more details in my post, sorry for that. I searced on -206 before posting the message but was not able to resolve my issue.
In one of our programs the cursor is defined as shown below
Code: |
DECLARE S012367K1 CURSOR FOR
SELECT
COUNT(*)
FROM TABLE1 T1,
TABLE2 T2
WHERE
T2.FIELD1 = T1.FIELD1
AND T2.FIELD2_DT > '2002-12-31'
AND FIELD3_DT = '0001-01-01'
AND FIELD4 = :SJ2K45.FIELD4
|
I think the issue is because of the hard coded values in the cursor declration, because when I remove the hard coded values and use the host varaibles its compiling with out any issues.
is it like we can not declare the hard coded values in the cursor?
Thank you _________________ Tx
Digger |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Thu Apr 16, 2009 3:54 pm Post subject: |
|
|
MFdigger wrote: | I think the issue is because of the hard coded values in the cursor declration, because when I remove the hard coded values and use the host varaibles its compiling with out any issues. is it like we can not declare the hard coded values in the cursor? Thank you |
Mfdigger,
Nope there is nothing wrong with hardcoded values. You are having errors on 4 different statements.
Code: |
STATEMENT=4688
STATEMENT=4844
STATEMENT=14634
STATEMENT=15022
|
which one did you correct? Since you are using a table joins pay extra attention and make sure that column names are correct and also check to see if you missed a comma or an and statement in between statements
Show us the 4688 and 4844 sql statements as is without any changes |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Thu Apr 16, 2009 4:00 pm Post subject: |
|
|
Digger,
*sigh**sigh**sigh********
are you serious about using a cursor to obtain a count without any qualifiying columns? what I mean there is of what use is the count? Singleton select should suffice.
is the plan binding after compile?
-206: ... In the case of a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement. ...
SJ2K45.FIELD4 - is this a column name from the dclgen or part of a cobol structure?
Quote: | is it like we can not declare the hard coded values in the cursor? |
no, that is not true. you can use literals in any sql, including a cursor declaration. _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
MFdigger Beginner
Joined: 09 Sep 2005 Posts: 124 Topics: 52 Location: Chicago
|
Posted: Thu Apr 16, 2009 4:25 pm Post subject: |
|
|
Hi Kolusu/Dick
Thank you for your info. I'm working in a conversion project.
In the below query also (as it is) when I change the Literal to Host variable its compiling with no issue.
STATEMENT=4844
Code: |
EXEC SQL
DECLARE @C02160001 CURSOR FOR
SELECT
DSTRCT_CD
FROM BXC02160 T1
WHERE
DLR_CD =
:C021600.DLRCD
AND TYPE_CD = 'P'
END-EXEC.
|
STATEMENT=4688 & 14634
Code: |
EXEC SQL
DECLARE @S0921J101 CURSOR FOR
SELECT
COUNT(*)
FROM BXS09210 T1,
BXS09220 T2
WHERE
T2.RCLL_NO = T1.RCLL_NO
AND T2.RCLL_STRT_DT > '1994-12-31'
AND RPR_DT = '0001-01-01'
AND VIN_CD =
:S0921J1.VINCD
END-EXEC.
|
STATEMENT=15022
Code: |
EXEC SQL
DECLARE @S05040002 CURSOR FOR
SELECT
LAST_UPDT_TM,
LAST_UPDT_CD,
HTLN_SYMP_TX
FROM BXT05040 T1
WHERE
HTLN_REF_CD =
:S050400.HTREFCD
FOR UPDATE OF
END-EXEC.
|
Is it any way related to compilation/Bind options? _________________ Tx
Digger |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Thu Apr 16, 2009 5:41 pm Post subject: |
|
|
MFdigger wrote: | Hi Kolusu/Dick
Is it any way related to compilation/Bind options? |
Mfdigger,
Yes it is related to your precompiler options. Try enclosing the the values in double quotes " instead of single quote and re-run the compile step. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Thu Apr 16, 2009 5:58 pm Post subject: |
|
|
Mfdigger,
I will let you figure out the compiler option which caused that problem. However I would recommend some coding standards. Code written should be readable and easy to follow
1. Start your EXEC SQL statements in column 12
2. Align your statements as shown below (select,from, where, and...)
3. Start your host variables at column 40
4. use comma's at the beginning of the variable rather than at the end of the variable as the column names can be of different lengths. It will be easier to read and follow
5. Do NOT suffix the table names with t1, t2 .. unless you have more than 1 table and you need to qualify the similar named columns
Code: |
----+----1----+----2----+----3----+----4----+----5----+----6
EXEC SQL
DECLARE @C02160001 CURSOR FOR
SELECT DSTRCT_CD
FROM BXC02160
WHERE DLR_CD = :C021600.DLRCD
AND TYPE_CD = 'P'
END-EXEC.
EXEC SQL
DECLARE @S0921J101 CURSOR FOR
SELECT COUNT(*)
FROM BXS09210 T1
,BXS09220 T2
WHERE T2.RCLL_NO = T1.RCLL_NO
AND T2.RCLL_STRT_DT > '1994-12-31'
AND RPR_DT = '0001-01-01'
AND VIN_CD = :S0921J1.VINCD
END-EXEC.
EXEC SQL
DECLARE @S05040002 CURSOR FOR
SELECT LAST_UPDT_TM
,LAST_UPDT_CD
,HTLN_SYMP_TX
FROM BXT05040
WHERE HTLN_REF_CD = :S050400.HTREFCD
FOR UPDATE OF LAST_UPDT_CD
END-EXEC.
|
|
|
Back to top |
|
|
CraigG Intermediate
Joined: 02 May 2007 Posts: 202 Topics: 0 Location: Viginia, USA
|
Posted: Fri Apr 17, 2009 7:40 am Post subject: |
|
|
Code: | instead of this
AND T2.RCLL_STRT_DT > '1994-12-31'
AND RPR_DT = '0001-01-01'
try this
AND T2.RCLL_STRT_DT > DATE('1994-12-31')
AND RPR_DT = DATE('0001-01-01')
|
|
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Fri Apr 17, 2009 10:02 am Post subject: |
|
|
CraigG,
I don't think it would make a difference adding a DATE function as the problem is single quote isn't being recognized. If you notice he got the same error where date is NOT involved (statement #4844)
Pre-compiler is NOT using the APOST compiler option
Kolusu |
|
Back to top |
|
|
MFdigger Beginner
Joined: 09 Sep 2005 Posts: 124 Topics: 52 Location: Chicago
|
Posted: Fri Apr 17, 2009 10:55 am Post subject: |
|
|
Hi Kolusu,
Thank you very much. Double quotes has worked for me.
In this project I was asked to create my own compile jcl and create tables and CICS transactions on my own and may be facing with all diff kinds of issues.
Thank you for all your help
The PARM options which are used in the compilation jcl are shown below where APOST is also been used.
Code: |
//COB EXEC PGM=IGYCRCTL,REGION=®,
// PARM='NODYNAM,LIB,OBJECT,RENT,RES,APOST,MAP,XREF'
|
I could able to compile the program with a MAXCC=8 and messages shows that the sub-programs were not included while compiling and these are called statically.
I compiled the sub-programs and also placed the load in the same library of the calling program but the spool is showing with the below messages:
Code: |
IEW2456E 9207 SYMBOL ZKY04K1 UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY.
IEW2456E 9207 SYMBOL ZKYB6K1 UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY.
IEW2456E 9207 SYMBOL ZKY06K1 UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY.
|
Could you please throw some light on this _________________ Tx
Digger |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Fri Apr 17, 2009 11:06 am Post subject: |
|
|
MFdigger,
Quote: | The PARM options which are used in the compilation jcl are shown below where APOST is also been used. |
You are looking at the compiler step, I said look at the PRECOMPILER step, that is where your SQL statements gets resolved.
Code: |
//DB2PRE EXEC PGM=DSNHPC,
// PARM='HOST(IBMCOB),XREF,SOURCE,FLAG(I),APOST'
|
Quote: | I could able to compile the program with a MAXCC=8 and messages shows that the sub-programs were not included while compiling and these are called statically. |
Your Programs should compile with a RC of 0 or 4. RC of 8 is an error |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Fri Apr 17, 2009 11:14 am Post subject: |
|
|
besides, it is not the compile step, but the binder (linkedit) step that resolves the CALL.
Quote: | Correct the reference or make the missing entry available to the Binder by (1) adding the member to SYSLIB, (2) adding an alias of that name to an existing member, (3) INCLUDEing a module which contains the missing entry point, (4) providing a LIBRARY control statement to direct the Binder to a different library during autocall processing, or (5) correct the source of the INCLUDE error. |
I imagine item 1 would solve your problem _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
MFdigger Beginner
Joined: 09 Sep 2005 Posts: 124 Topics: 52 Location: Chicago
|
Posted: Mon Apr 20, 2009 10:51 am Post subject: |
|
|
Thank you Dick/Kolusu. This issue got solved for me _________________ Tx
Digger |
|
Back to top |
|
|
|
|