MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

BIND issuess

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
MFdigger
Beginner


Joined: 09 Sep 2005
Posts: 124
Topics: 52
Location: Chicago

PostPosted: Thu Apr 16, 2009 12:04 pm    Post subject: BIND issuess Reply with quote

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
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Thu Apr 16, 2009 12:18 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 16, 2009 12:39 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
MFdigger
Beginner


Joined: 09 Sep 2005
Posts: 124
Topics: 52
Location: Chicago

PostPosted: Thu Apr 16, 2009 3:33 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 16, 2009 3:54 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Thu Apr 16, 2009 4:00 pm    Post subject: Reply with quote

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
View user's profile Send private message
MFdigger
Beginner


Joined: 09 Sep 2005
Posts: 124
Topics: 52
Location: Chicago

PostPosted: Thu Apr 16, 2009 4:25 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 16, 2009 5:41 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 16, 2009 5:58 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
CraigG
Intermediate


Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA

PostPosted: Fri Apr 17, 2009 7:40 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Apr 17, 2009 10:02 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
MFdigger
Beginner


Joined: 09 Sep 2005
Posts: 124
Topics: 52
Location: Chicago

PostPosted: Fri Apr 17, 2009 10:55 am    Post subject: Reply with quote

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=&REG,                     
//       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 Rolling Eyes
_________________
Tx
Digger
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Apr 17, 2009 11:06 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Fri Apr 17, 2009 11:14 am    Post subject: Reply with quote

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
View user's profile Send private message
MFdigger
Beginner


Joined: 09 Sep 2005
Posts: 124
Topics: 52
Location: Chicago

PostPosted: Mon Apr 20, 2009 10:51 am    Post subject: Reply with quote

Thank you Dick/Kolusu. This issue got solved for me
_________________
Tx
Digger
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group