IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL CODE 206


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Wed Oct 10, 2018 9:20 pm
Reply with quote

Hi ,

I am getting SQL code 206 when I am running below code from program.

Code:
SELECT  VZSPA.PARAM_VALUE
INTO    :VZDSPA.PARAM-VALUE
        :VZLSPA-NULL.IND-PARAM-VALUE
FROM    VVZSPA VZSPA
WHERE   VZSPA.VZSPD_DEF_PK1       =
        :VZPC202I-SEARCH.VZSPD-DEF-PK1
AND     VZSPA.PARAM_EFF_DT        =
        (SELECT COALESCE(MAX(VZSPA1.PARAM_EFF_DT),
                         DATE('31.12.9999'))
         FROM VVZSPA VZSPA1
         WHERE VZSPA1.VZSPD_DEF_PK1 =
                         :VZPC202I-SEARCH.VZSPD-DEF-PK1
         AND   VZSPA1.PARAM_EFF_DT <= CURRENT_DATE
         AND ((:VZPC202I-SEARCH.VZNSC-FOR-FK4 = '  '
                AND VZSPA1.VZNSC_FOR_FK4 IS NULL)
             OR VZSPA1.VZNSC_FOR_FK4   =
                       :VZPC202I-SEARCH.VZNSC-FOR-FK4))



Same query, when I am running on SPUFI, its running perfect.


Code:
SELECT  VZSPA.PARAM_VALUE
--INTO    :VZDSPA.PARAM-VALUE
--        :VZLSPA-NULL.IND-PARAM-VALUE
FROM    ygt.VVZSPA VZSPA
WHERE   VZSPA.VZSPD_DEF_PK1       = '73'
--        :VZPC202I-SEARCH.VZSPD-DEF-PK1
AND     VZSPA.PARAM_EFF_DT        =
        (SELECT COALESCE(MAX(VZSPA1.PARAM_EFF_DT),
                         DATE('31.12.9999'))
         FROM ygt.VVZSPA VZSPA1
         WHERE VZSPA1.VZSPD_DEF_PK1 = '0073'
--                         :VZPC202I-SEARCH.VZSPD-DEF-PK1
         AND   VZSPA1.PARAM_EFF_DT <= CURRENT_DATE
         AND ((--:VZPC202I-SEARCH.VZNSC-FOR-FK4
                                    '  ' = '  '
                AND VZSPA1.VZNSC_FOR_FK4 IS NULL)
             OR VZSPA1.VZNSC_FOR_FK4   = '  '
             --          :VZPC202I-SEARCH.VZNSC-FOR-FK4

))
Note: -In program, I am getting VZNSC-FOR-FK4 as a blank and VZSPD-DEF-PK1 as a 0073
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Oct 10, 2018 9:34 pm
Reply with quote

Start looking at sysout for Object-Name or Column Name when SQLCODE -206 or SQLCODE +206 is issued.

Why do you have two host variables in INTO ( that too without ',')when there is only one column in SELECT?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Oct 10, 2018 10:01 pm
Reply with quote

When you mention sql error code include a negative sign.

What is the object-name when you run into trouble? This should give you an idea on what is wrong.

Quote:
-206 object-name IS NOT VALID IN THE CONTEXT WHERE IT IS USED


Explanation


An object is specified in a context where it is not valid.
object-nameThe name of the object that could not be resolved.


Rohit, second host variable should be a null indicator.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Oct 10, 2018 11:22 pm
Reply with quote

This is more appropriate representation of using the NULL indicator if that's so.
Code:
SELECT  VZSPA.PARAM_VALUE
 INTO    :VZDSPA.PARAM-VALUE :VZLSPA-NULL.IND-PARAM-VALUE
 FROM    VVZSPA VZSPA
 WHERE   VZSPA.VZSPD_DEF_PK1       =
         :VZPC202I-SEARCH.VZSPD-DEF-PK1
 AND     VZSPA.PARAM_EFF_DT        =
         (SELECT COALESCE(MAX(VZSPA1.PARAM_EFF_DT),
                          DATE('31.12.9999'))
          FROM VVZSPA VZSPA1
          WHERE VZSPA1.VZSPD_DEF_PK1 =
                          :VZPC202I-SEARCH.VZSPD-DEF-PK1
          AND   VZSPA1.PARAM_EFF_DT <= CURRENT_DATE
          AND ((:VZPC202I-SEARCH.VZNSC-FOR-FK4 = '  '
                 AND VZSPA1.VZNSC_FOR_FK4 IS NULL)
              OR VZSPA1.VZNSC_FOR_FK4   =
                        :VZPC202I-SEARCH.VZNSC-FOR-FK4))

However, I doubt how BIND step executed successfully ? SQLCODE -206 should come during the BIND and not during the execution unless you set off Validate NO for BIND.
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Thu Oct 11, 2018 1:43 pm
Reply with quote

This is SQLCA displayed on CICS when I am executing this program:-

Code:
SQL COMMUNICATION AREA:
 SQLCABC      = 136
 SQLCODE      =   -206
 SQLERRML     = 010
 SQLERRMC     = '31.12.9999'
 SQLERRP      = 'DSNXORSO'
 SQLERRD(1-6) = -100,  000, 00000, -1, 00000,  000
 SQLWARN(0-A) = '_ _ _ _ _ _ _ _ _ _ _'
 SQLSTATE     = 42703


My Bind step is successfully. Infact in same program, my first query is running fine and failing on this second query.
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Thu Oct 11, 2018 6:52 pm
Reply with quote

Below lines of code is working fine:-
SELECT VZSPA.PARAM_VALUE
FROM VVZSPA VZSPA
WHERE VZSPA.VZSPD_DEF_PK1 = '0073'
AND VZSPA.PARAM_EFF_DT <= date(12/31/9999)


but when I am trying to use below lines of code, its failing with SQL code 206.

SELECT VZSPA.PARAM_VALUE
FROM VVZSPA VZSPA
WHERE VZSPA.VZSPD_DEF_PK1 = '0073'
AND VZSPA.PARAM_EFF_DT <= date('31.12.9999')


any pointer for causing this issue
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 7:45 pm
Reply with quote

Quote:
This is SQLCA displayed on CICS when I am executing this program:-

Where is the "ERROR:" part of SQLCODE?
I suggest you talk to DBA and find out why it failed because I tried at my site and it worked. So something to do with Environment settings or DB2 version.
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Thu Oct 11, 2018 8:07 pm
Reply with quote

One thing that springs to mind is the difference in the code...

Code:
AND VZSPA.PARAM_EFF_DT <= date(12/31/9999)

and
Code:
AND VZSPA.PARAM_EFF_DT <= date('31.12.9999')


these date formats are very different and the SQLCA for the -206 shows

Code:
SQLERRMC     = '31.12.9999'


Garry
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 8:16 pm
Reply with quote

Garry, Do you think its a problem with EUR format or use of a DATE function or I missed the point?
Quote:
Below lines of code is working fine:-
SELECT VZSPA.PARAM_VALUE
FROM VVZSPA VZSPA
WHERE VZSPA.VZSPD_DEF_PK1 = '0073'
AND VZSPA.PARAM_EFF_DT <= date(12/31/9999)
I am sure this will not work unless you specify Dates in single quotes, so how did it work?
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Thu Oct 11, 2018 8:20 pm
Reply with quote

I wouldn't be sure, Rohit - it just that there are differences. The working version does not have quotes and the format is mm/dd/yyyy while the failing version has quotes and the format is dd.mm.yyyy .

Garry
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 8:22 pm
Reply with quote

You are right, no dates format would work without quotes. It highly looks like a typo. Moreover, even with missing quotes TS should never get -206 for that. We don't know what he is posting here is as same as he has actually tried.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Oct 11, 2018 10:02 pm
Reply with quote

If you have got -206 at bind time it's time to check your code

Edit: Hope you have double checked your Bind Jcl too
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 11:07 pm
Reply with quote

Quote:
If you have got -206 at bind time it's time to check your code
Did you address it to TS? then TS already acknowledged it passed the BIND. I don't know how more to trust TS since we don't have evidence of it passed the BIND but TS can be right if VALIDATE(RUN) is provided in BIND card which issues Warning messages and pass the BIND.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts REXX code to expand copybook in a cob... CLIST & REXX 2
No new posts VSAM return code 23 - for a Random read COBOL Programming 4
No new posts Monitoring production job progress. N... JCL & VSAM 4
Search our Forums:

Back to Top