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

DB2-V8 Vs DB2-V9 problem


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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 8:34 pm
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

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 8:46 pm
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 8:49 pm
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

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 8:52 pm
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 8:53 pm
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

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 8:55 pm
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 8:56 pm
Reply with quote

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

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 8:59 pm
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
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 9:02 pm
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 9:06 pm
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

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 9:10 pm
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 9:15 pm
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
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 9:32 pm
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
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

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 9:35 pm
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

Global Moderator


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

PostPosted: Fri Sep 07, 2012 10:17 pm
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
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
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
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Sep 07, 2012 10:45 pm
Reply with quote

just for grins,
what is the pic clause for f1-print-a?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Sep 07, 2012 11:35 pm
Reply with quote

Hello,

Quote:
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
IBM (as well as many other software vendors) ensures the "new reease" supports what was done correctly in the "old" release. Just because something will run, does not mean it was done correctly . . . (As DBZ has mentioned)

If many, many customers have been caught by the same situation, there may be an IBM workaround or fix available. You need to ask IBM support what choices you might have.
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 11:53 pm
Reply with quote

is f1-print-a a PIC 9(?) or PIC S9(?) (display).
db2 stopped messing with that garbage cobol data type.

also, the reason SPUFI works:
you are using Literals and not HOST-VARIABLES.
db2 can determine the data type from the literal
(maybe not what you mean, but db2 can determine a definite data type)
but has stopped trying to figure out some host variables.

when you use functions like DEC, you should CAST the host-variable.
DISPLAY is really x-type and if you try to use a scalar function
that requires a numeric data type, you are going to have problems.
(trying to blame it on anyone except your sub-par coders)

even though IBM has really dumbed-down COBOL,
if you think db2 vsn9 is strict,
wait til you migrate to 10.
all the BS SQL your people have written, is going to keep you busy.
Back to top
View user's profile Send private message
razesh84

New User


Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India

PostPosted: Sat Sep 08, 2012 1:20 am
Reply with quote

Rohit,

I don't consider myself a sql expert,but seriously that case statement makes no sense.When you have all the information why ask DB2 to manipulate data for you.Use cobol logic to get the value & pass DB2 for insert(As Dick already suggested).

Also the problem is nothing to do with GTT.you can code such query against any tables & run in DB2V9.You'll be getting the same error.

And now comes how to solve this issue?

1> you can always fight with IBM
2> accept the fact(that it was really bad programming) & change the code
3> change the code under performance initiative bucket.though I'm not sure how many times the insert was executed but still if you follow dick's approach you will get some savings.

This is a real good example of Dick's signature comment.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Sat Sep 08, 2012 8:38 am
Reply with quote

and another thing.

it appears that there is no CALL to DSNTIAR,
otherwise you would have told us the offending host-variables name, n'est-ce pas?

If not, suggest that your site enter the computer age
and incorporate a CALL DSNTIAR for every SQLCODE < 0
in all of your programs.

If so, it would help to know what variable was mentioned.
Quote:
all the variables used in the CASE are unrecognized.

is BS.
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 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts z/vm installation problem All Other Mainframe Topics 0
No new posts Job scheduling problem. JCL & VSAM 9
No new posts Problem with IFTHEN=(WHEN=GROUP,BEGIN... DFSORT/ICETOOL 5
No new posts Need to add field to copybook, proble... COBOL Programming 14
Search our Forums:

Back to Top