Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL CODE 206

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 29
Location: India

PostPosted: Wed Oct 10, 2018 9:20 pm    Post subject: SQL CODE 206
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Wed Oct 10, 2018 9:34 pm    Post subject:
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: 444
Location: USA

PostPosted: Wed Oct 10, 2018 10:01 pm    Post subject: Reply to: SQL CODE 206
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Wed Oct 10, 2018 11:22 pm    Post subject:
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: 29
Location: India

PostPosted: Thu Oct 11, 2018 1:43 pm    Post subject:
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: 29
Location: India

PostPosted: Thu Oct 11, 2018 6:52 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Thu Oct 11, 2018 7:45 pm    Post subject:
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: 1025
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Thu Oct 11, 2018 8:07 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Thu Oct 11, 2018 8:16 pm    Post subject:
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: 1025
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Thu Oct 11, 2018 8:20 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Thu Oct 11, 2018 8:22 pm    Post subject:
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

Moderator


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

PostPosted: Thu Oct 11, 2018 10:02 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2031
Location: NY,USA

PostPosted: Thu Oct 11, 2018 11:07 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Invoke WebService failing with respon... Ashishpanpaliya CICS 3 Thu Aug 09, 2018 3:14 pm
This topic is locked: you cannot edit posts or make replies. IO-PCB status code AL srinianusuya IMS DB/DC 1 Mon Aug 06, 2018 10:26 am
No new posts SQL Code 302 while executing stored ... Ashishpanpaliya DB2 8 Wed Aug 01, 2018 7:54 pm
No new posts sql code 331 while calling Stored Pro... Ashishpanpaliya DB2 5 Wed Aug 01, 2018 7:06 pm
No new posts Code char set conversion from IBM-420... thelefthandofdarkness IBM Tools 4 Thu Jul 26, 2018 3:03 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us