Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2-V8 Vs DB2-V9 problem
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 8:34 pm    Post subject: DB2-V8 Vs DB2-V9 problem
Reply with quote

Hello Team,

We have faced the below issue after migration,

We have one insert query in GTT and during the insert into GTT we have used the below CASE fuction for one of the column,

Code:
,(case
                when abc.txn_q = 0 and abc.txn_px_a = 0 then ' '
                else char(decimal(abc.txn_px_a,11,4))
                end)


This perticular CASe works fine till it was running on DB2-V8 but as soon as we ran this on DB2-V9 it abended with SQLCODE -312.

Why this fuction doesn't support in DB2-V9/ any possible overcome on this?

Thanks,
Rohit
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 07, 2012 8:46 pm    Post subject:
Reply with quote

Hello,

Why did you not post the variable name that caused the -312?

Have you read about the -312 in the DB2 Messages and Codes manual? The reason appears quite clear. Is this dynamic SQL or a Trigger?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 8:49 pm    Post subject: Reply to: DB2-V8 Vs DB2-V9 problem
Reply with quote

DB2-V8 explaination:

Code:
-312   variable-name IS AN UNDEFINED OR UNUSABLE HOST VARIABLE OR IS USED 
       IN A DYNAMIC SQL STATEMENT OR A TRIGGER DEFINITION   



DB2-V9 explaination:


Code:
 -312   VARIABLE variable-name IS NOT DEFINED OR NOT USABLE


I am not using neither dynamic SQLs or Triggers.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 07, 2012 8:52 pm    Post subject:
Reply with quote

Hello,

Yup, that is what the manuals say, but which variable name causes the problem?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 8:53 pm    Post subject:
Reply with quote

The particular CASE statement, which I have given above during the INSERT to GTT.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 07, 2012 8:55 pm    Post subject:
Reply with quote

Hello,

Again, WHAT is the NAME of the VARIABLE that causes the problem?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 8:56 pm    Post subject: Reply to: DB2-V8 Vs DB2-V9 problem
Reply with quote

all the variables used in the CASE are unrecognized.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 07, 2012 8:59 pm    Post subject:
Reply with quote

Hello,

You could try without the "abc." or find and use the new prefix? Which other queries that run ok specify "abc.txn_px_a".

What happens if you run this CASE in SPUFI?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 07, 2012 9:02 pm    Post subject:
Reply with quote

why the () around the case statement?

show your complete sql.
just showing us one line is not going to help,
we have no idea of context or scope.

db2 always tightens-up the validation of sql with a new release.
you did something in this sql which was not really standard,
by v8 let you get-away with it.
v9 said, enough, clean-up your sql.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 9:02 pm    Post subject: Reply to: DB2-V8 Vs DB2-V9 problem
Reply with quote

Actually, alias is must for me to give here, also CASE works fine in SPUFI but when we do INSERT to GTT then it gives problem.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 9:06 pm    Post subject: Reply to: DB2-V8 Vs DB2-V9 problem
Reply with quote

Code:
exec sql                 
     insert into ABCD111
     (                 f_abc_a1,
                       f_abc_a2 )
    values
     ( :f-abc-a1,
       (case                                         
         when :f1-txn-q=0  and :f1-print-a=0 then ' '
                else char(decimal(:f1-print-a,11,4))         
        end)                                         
     ) for :f-row-count ROW
end-exec

So this the query which was working in production for past couple of years with DB2-V8 now this is giving SQLCODE of -312.

Note:ABCD111 is a GTT[/code]
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 07, 2012 9:10 pm    Post subject:
Reply with quote

Hello,

Quote:
So this the query which was working in production for past couple of years with DB2-V8 now this is giving SQLCODE of -312.

Plesase re-read what DBZ posted.

Why are there dashes in the column names in the CASE?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 9:15 pm    Post subject:
Reply with quote

Dick,

Quote:
Why are there dashes in the column names in the CASE?


The above INSERT query I have provided is of my running production query on DB2-V8 also dashes are like my host variables here. Sorry if any misunderstanding is created here.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 07, 2012 9:28 pm    Post subject:
Reply with quote

obviously, your host variables are not valid for the functions being executed against them.

if i was db2 i would have said,
why the extra requirement of resources?
(using db2 to do simple editing)
when all of this:
Code:
case                                         
         when :f1-txn-q=0  and :f1-print-a=0 then ' '
                else char(decimal(:f1-print-a,11,4))         
        end


could have been done in cobol code.
some smart-ass decided to use db2 instead of cobol code
to generate a value for ':f-abc-a2'
and now your are suffering the consequences of a really poor sql coder.

i would double check all modules where this idiot laid sql.
being clever and using db2 functions to manipulate host variables
is indicative of someone who does not understand
how to program on a mainframe (more than likely came from pc).
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Sep 07, 2012 9:32 pm    Post subject:
Reply with quote

Yup, but I am support guy so I have to answer this and provide them solutions. icon_smile.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 07, 2012 9:35 pm    Post subject:
Reply with quote

stop wasting time trying to perfect the case statement.

replace the case statement with :f-abc-a2
and populate :f-abc-a2 with cobol code before you execute the INSERT

that is the solution.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 07, 2012 9:35 pm    Post subject: Reply to: DB2-V8 Vs DB2-V9 problem
Reply with quote

Hello,

Surely not what you/they want to hear, but the code needs to be corrected . . . icon_neutral.gif

Unfortunately, it has worked for a while - better if it had failed on initial implementation. . .

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

Senior Member


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

PostPosted: Fri Sep 07, 2012 10:17 pm    Post subject:
Reply with quote

Thanks All.
But we found couple more instances related to GTT, so I feel as far as GTT is concern whatever DB2-V8 supports should also comes up with DB2-V9 package, otherwise changing the exising code is a big impact and needs a grat manual efforts.icon_smile.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 07, 2012 10:28 pm    Post subject:
Reply with quote

well, that you have to take up with IBM.

IBM does, by the way, provide documentation for migration activities,
included are any gotcha's.
a gotcha is something that has been discontinued.

Had your (i realize it is not your code) SQL been properly written,
you would not have the problem.
and properly written does not mean that it manages to work
because a particular version of db2 allows some sloppiness.

besides, most sites do testing prior to migration
or are you in the testing phase?

I will be surprised if it has anything to do with GTT.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 07, 2012 10:38 pm    Post subject:
Reply with quote

if you want to perfect (actually just bring it up to standards) the case:

Probably not:
Quote:
Verify that the program or SQL routine contains a declaration for the variable. Verify that the variable name in the SQL statement is spelled correctly.

More than likely the cause:
Quote:
Verify that the attributes of the variable are compatible with its use in the statement.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm
No new posts TXT2PDF problem - printing @ in PDF vipul65 All Other Mainframe Topics 9 Thu Aug 25, 2016 12:48 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us