|
View previous topic :: View next topic
|
| Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Yup, that is what the manuals say, but which variable name causes the problem? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| The particular CASE statement, which I have given above during the INSERT to GTT. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Again, WHAT is the NAME of the VARIABLE that causes the problem? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| all the variables used in the CASE are unrecognized. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
Yup, but I am support guy so I have to answer this and provide them solutions.  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Surely not what you/they want to hear, but the code needs to be corrected . . . 
Unfortunately, it has worked for a while - better if it had failed on initial implementation. . .
d |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
just for grins,
what is the pic clause for f1-print-a? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
razesh84
New User
Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
