473,708 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Getting SQLCODE -302 after migrating DB2 V7 to V8 IN ibm z/os

Sri
All

Recenetly our shop migrated to DB2 V8 from V7. We are in IBM System Level:
z/OS 1.6.1 @ RSU 0702.
Processor : IBM 2064-1C7 (z/900) # 1B89 Mode: 64-bit
One of my application is facing problem while testing their code/Jobs in DB2
V8 environments. The Details are: Job is abending with SQLCODE -302 SQL
error on the prepare staement. There is no code modification made for many
years. It is working fine in db2 V7 enviornments. The module is using
dynamic sql prepare statement.
Its a cobol/db2 module.

EXEC SQL
PREPARE SQL_STM6 FROM :WS06-GET-DUPLICATE-CURSOR
END-EXEC.

IF SQLCODE = 0
THEN
SET SW14-PREPARED TO TRUE
PERFORM 33110-DECLARE-GET-DUPL-USAGE
ELSE
PERFORM C9000-DB-ERROR
DISPLAY 'PREPARE GET_DUPLICATE FAILED, SQL= ' SQLCODE
END-IF.
Any idea we need to change the code for v8 or is the probelm with Db2 v8
migration.

Thanks
Ravi
Jun 2 '07 #1
22 6653
In my shop it took over a year to convert from DB2 version 7 to
Version 8, due to the large number of test environments requiring
concurrent support and the change to Unicode in version 8. As I
recall, several systems on PC's and Unix boxes required changes to
commit logic because their code would work with version 7 but not with
version 8.

But I am on the applications side and I am no DB2 expert. Have you
talked with your DBA or technical services people about this problem?
They may be able to suggest a solution or assist you with diagnosing
the probleml.

We are not allowed to use dynamic SQL in our environment due to the
security risks and GLBA (Gramm-Leach-Bliley Act).
http://en.wikipedia.org/wiki/Gramm-Leach-Bliley_Act

With kindest regards,

Sri wrote:
All

Recenetly our shop migrated to DB2 V8 from V7. We are in IBM System Level:
z/OS 1.6.1 @ RSU 0702.
Processor : IBM 2064-1C7 (z/900) # 1B89 Mode: 64-bit
One of my application is facing problem while testing their code/Jobs in DB2
V8 environments. The Details are: Job is abending with SQLCODE -302 SQL
error on the prepare staement. There is no code modification made for many
years. It is working fine in db2 V7 enviornments. The module is using
dynamic sql prepare statement.
Its a cobol/db2 module.

EXEC SQL
PREPARE SQL_STM6 FROM :WS06-GET-DUPLICATE-CURSOR
END-EXEC.

IF SQLCODE = 0
THEN
SET SW14-PREPARED TO TRUE
PERFORM 33110-DECLARE-GET-DUPL-USAGE
ELSE
PERFORM C9000-DB-ERROR
DISPLAY 'PREPARE GET_DUPLICATE FAILED, SQL= ' SQLCODE
END-IF.
Any idea we need to change the code for v8 or is the probelm with Db2 v8
migration.

Thanks
Ravi

--
http://arnold.trembley.home.att.net/

Jun 2 '07 #2
Arnold Trembley wrote:
We are not allowed to use dynamic SQL in our environment due to the
security risks and GLBA (Gramm-Leach-Bliley Act).
http://en.wikipedia.org/wiki/Gramm-Leach-Bliley_Act
Is that just a step your organization has taken to comply, or do we
actually have a law on the books prohibiting Dynamic SQL?

--
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelow domain ~
~ _____ / \ | ~ http://www.djs-consulting.com/linux/blog ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w$ ~
~ !O M-- V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e ~
~ h---- r+++ z++++ ~
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~

"Who is more irrational? A man who believes in a God he doesn't see, or
a man who's offended by a God he doesn't believe in?" - Brad Stine
Jun 2 '07 #3
I'll ask the dumb question: what does "-302" mean and how is it derived?

Maybe that would provide what a police detective would call a "clue."
MCM

Jun 2 '07 #4
Michael Mattias wrote:
I'll ask the dumb question: what does "-302" mean and how is it
derived?
Maybe that would provide what a police detective would call a "clue."
MCM
<quote>
-302
THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID
OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
Explanation:
DB2 received data that was invalid or too large to fit in the corresponding
column of the table or the corresponding target value. The position-number
identifies either the host variable number (if the message is issued as a
result of an INSERT, UPDATE, DELETE, SELECT, VALUES INTO, or SET assignment
statement), or the parameter number (if the message is issued as the result
of a CALL statement or the invocation of a function).

<snip further details, see link below>

Programmer response:

Correct the application program, function or stored procedure. Check the
column type and length of the value or the data type and contents of the
input host variable or parameter position-number. Ensure that the value of
the host variable or parameter will fit in the column or contains valid
decimal data. Valid decimal data is a System/370 packed decimal number.

SQLSTATE:
22003 if number too large for target; 22001 otherwise.
</quote>

Source:
http://publib.boulder.ibm.com/infoce...oc.mc/nsql.htm

--
Jeroen
Jun 2 '07 #5
"The Boss" <us****@No.Spam .Please.invalid wrote in message
news:46******** *************@n ews.xs4all.nl.. .
Michael Mattias wrote:
>I'll ask the dumb question: what does "-302" mean and how is it
derived?
Maybe that would provide what a police detective would call a "clue."
<quote>
-302
THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID
OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
Explanation:
DB2 received data that was invalid or too large to fit in the
corresponding column of the table or the corresponding target value. The
position-number identifies either the host variable number (if the message
is issued as a result of an INSERT, UPDATE, DELETE, SELECT, VALUES INTO,
or SET assignment statement), or the parameter number (if the message is
issued as the result of a CALL statement or the invocation of a function).
So how do you get that on a PREPARE?

Only thing which remotely comes to mind along these lines is that something
in the WS definition of the parameters associated with the statement
(statement 'SQL_STM6' conveniently not shown) is using say, a PIC 9(9) for
the parameter (e.g. INSERT INTO tablename SET columnname=:WS-PARAM-VARIABLE)
where the bound column is a DECIMAL (6,0) or something like that.

MCM
Jun 2 '07 #6
CG
Sri wrote:
All

Recenetly our shop migrated to DB2 V8 from V7. We are in IBM System Level:
z/OS 1.6.1 @ RSU 0702.
<SNIP. . . There is no code modification made for many
years. It is working fine in db2 V7 enviornments. The module is using
<SNIP>
Any idea we need to change the code for v8 or is the probelm with Db2 v8
migration.

Thanks
Ravi
At the risk of raising the wrath of some on this list, ...

The first question I have is which compiler was used to compile the
program? Depending on the 'mode' you are running DB2 V8, you may be
running into the minimum level of the compiled code required for DB2 V8.
Specifically, to run in New Function Mode [NFM], your COBOL program
must have been compiled by Enterprise COBOL V3.2 or later.

There are actually three modes for running DB2 V8:
1) Compatibility Mode (CM) is acceptable provided the code was
successfully running with DB2 V7. In this case, as long as you meet the
compiler level for V7, you're OK.
2) Before you can move to NFM, you must pass through the Enabling New
Function Mode [ENFM]. This is basically the stage where you meet the
prerequisites for NFM, you have started some of the migration steps, but
you have not attempted to exploit the new functions in V8.
3) Then finally, NFM.

Ref: DB2 UDB for z/OS V8 Release Planning Guide (SC18-7425-02)
Chapter 3. Planning for migration, conversion, and fallback

So, if you really have the situation "...There is no code modification
made for many years...," then I would suspect that you might have a
problem with the compiler level of your program.

Carl
Jun 3 '07 #7
In article <58************ *************** @FUSE.NET>,
CG <Ca************ **********@MCGC G.Comwrote:

[snip]
>So, if you really have the situation "...There is no code modification
made for many years...," then I would suspect that you might have a
problem with the compiler level of your program.
'What's wrong with Your Program? It's throwing garbage results!'

'Hmmmmm... according to the load module it hasn't been touched in thirteen
years or so... what do the data look like?'

'We're getting the same data as always, from SmallCo... there's something
wrong with Your Program!'

'SmallCo... didn't I read that they were acquired and are now a subsidiary
of BigCo?'

'So what... there's something wrong with Your Program!'

'Let me take a look at the data... how interesting... you know, it looks
like you're receiving comma-delimited ASCII input now, the program was
designed for EBCDIC files.'

'Look, everyone knows I'm Not Technical... but Your Program's throwing
garbage, do something about it!'

DD

Jun 3 '07 #8
Sri
Iam pretty sure the DB2 V8 is in CM mode. System DBAs informred this one.
The compiler version is PP 5655-G53 IBM Enterprise COBOL for z/OS 3.3.1.

**DISPLAY 'PREPARE GET DUPL USAGE'.
EXEC SQL
PREPARE SQL_STM6 FROM :WS06-GET-DUPLICATE-CURSOR
END-EXEC.

**DISPLAY 'DECLARE GET DUPL USAGE'.

EXEC SQL
DECLARE GET_DUPLICATE CURSOR FOR SQL_STM6
END-EXEC.
"CG" <Ca************ **********@MCGC G.Comwrote in message
news:58******** *************** ****@FUSE.NET.. .
Sri wrote:
>All

Recenetly our shop migrated to DB2 V8 from V7. We are in IBM System
Level: z/OS 1.6.1 @ RSU 0702.
<SNIP. . . There is no code modification made for many years. It is
working fine in db2 V7 enviornments. The module is using <SNIP>
Any idea we need to change the code for v8 or is the probelm with Db2 v8
migration.

Thanks
Ravi

At the risk of raising the wrath of some on this list, ...

The first question I have is which compiler was used to compile the
program? Depending on the 'mode' you are running DB2 V8, you may be
running into the minimum level of the compiled code required for DB2 V8.
Specifically, to run in New Function Mode [NFM], your COBOL program must
have been compiled by Enterprise COBOL V3.2 or later.

There are actually three modes for running DB2 V8:
1) Compatibility Mode (CM) is acceptable provided the code was
successfully running with DB2 V7. In this case, as long as you meet the
compiler level for V7, you're OK.
2) Before you can move to NFM, you must pass through the Enabling New
Function Mode [ENFM]. This is basically the stage where you meet the
prerequisites for NFM, you have started some of the migration steps, but
you have not attempted to exploit the new functions in V8.
3) Then finally, NFM.

Ref: DB2 UDB for z/OS V8 Release Planning Guide (SC18-7425-02)
Chapter 3. Planning for migration, conversion, and fallback

So, if you really have the situation "...There is no code modification
made for many years...," then I would suspect that you might have a
problem with the compiler level of your program.

Carl

Jun 3 '07 #9


LX-i wrote:
Arnold Trembley wrote:
>We are not allowed to use dynamic SQL in our environment due to the
security risks and GLBA (Gramm-Leach-Bliley Act).
http://en.wikipedia.org/wiki/Gramm-Leach-Bliley_Act


Is that just a step your organization has taken to comply, or do we
actually have a law on the books prohibiting Dynamic SQL?
I'm not a lawyer, and I am not aware of any law prohibiting Dynamic
SQL. Here's the problem, based on this quote from Wikipedia:

"In terms of compliance, the key rules under the Act include The
Financial Privacy Rule which governs the collection and disclosure of
customers’ personal financial information by financial institutions.
It also applies to companies, regardless of whether they are financial
institutions, who receive such information. The Safeguards Rule
requires all financial institutions to design, implement and maintain
safeguards to protect customer information. The Safeguards Rule
applies not only to financial institutions that collect information
from their own customers, but also to financial institutions – such as
credit reporting agencies – that receive customer information from
other financial institutions."

My understanding of the issue is that if data is taken from the web
and inserted into a variable used by dynamic SQL then there is a
possibility that personal financial data could be altered or publicly
exposed.

I don't really think it's a huge issue, because I'm not aware that we
use dynamic SQL in any mainframe applications. And we don't take
input from the public internet and put it into SQL statements. But
there is a lot of concern about the security of databases connected to
the web.

So the short answer to your question is it's a step my organization is
taking to reduce its risk.
--
http://arnold.trembley.home.att.net/

Jun 3 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
45895
by: Jean-Marc Blaise | last post by:
Dear all, Could you explain why some SQL messages do not possess a SQLSTATE ? Why not transmitting the SQLSTATE of the original <sqlcode>, for example if you get a -911 on a RUNSTATS ? SQL2310N The utility could not generate statistics. Error "<sqlcode>" was returned.
15
7239
by: Twan Kennis | last post by:
Hi, I have a DB2 database on the IBM iSeries platform, on which I created several Stored Procedures with the SQLCODE as a return-parameter. These Stored Procedures are called from a Windows application (build in Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC driver. On the Internet I found a whitepaper "SQL messages and codes" provided by IBM which describes a complete list of all SQLSTATEs, SQLCODEs and
2
3698
by: scotdb | last post by:
I'm trying to get the SQLERRMC info from the SQLCA into my SP so that I can use the information it provides. I'm successfully getting the SQLCODE and SQLSTATE and so added the SQLERRMC to the code which obtains these. It doesn't seem to work however - create procedure dbair001.sp001testerr ( ,OUT p_sqlstate CHAR(5) ,OUT p_sqlcode INTEGER ,OUT p_sqlerrmc VARCHAR(70)
0
2145
by: rpisaneschi | last post by:
Can anyone figure out why I am getting the SQL 203 below ? SELECT DISTINCT SP.SPCLT_CD, CASE CI.CORP_ENT_CD WHEN 'IL1' THEN ' ' ELSE SP.PROV_TYP_CD END AS XPROV_TYP_CD, ST.PWN_STA_CD FROM
10
13219
by: scoonie999 | last post by:
I'm having a problem that I can't seem to find any solution for online. I'm using a cursor in a cobol program to fetch some data. I know for a fact that the select should return 2 rows. The fetch works as I would expect it to on the 2 rows, but then when I do my next fetch, the program ends abnormally. My next step is to check for sqlcode 100, as I would expect it to be, but my program never even gets to this step.
6
60131
by: Srireddy | last post by:
Hi, I was trying to update a column in a table in my DB using java code but after updating some rows it throws an exception like "exception in processcom.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: NULLID.SYSLH203 0X5359534C564C3031". Can anyone have any idea about this, if some one has has any solution plz reply back ASAP... thanks in advance for your help.
1
11215
by: jyoti202 | last post by:
Hi, Need help for this as i have been looking for it but could not get any results. We are using java as front end and DB2 as backend, i m getting the exception while executing a particular query with following error description: DB2 SQL error: SQLCODE: -901, SQLSTATE: 58004, SQLERRMC: sqlno_itr_plan::next :rc( 0) SQLCODE: -901 this error code says "Non severe System error" This query was working fine till...
2
11265
by: monika thotha | last post by:
i m working on mainframes, coding in cobol and db2 EXEC SQL UPDATE BOOKS_STAT17 SET STATUS1 = 'O' , EMP_NUMBER=NULL WHERE COPY_NUMBER = WS-COPY-NUM AND ISBN_NUMBER = WS-ISBN-NUM END-EXEC. error i m getting: DBH3 BIND SQL ERROR USING TRG4SUSR AUTHORITY
3
8733
by: jominp | last post by:
When i am trying to execute,the following query using hibernate select * from from user where (date(MONTH || '-' || '01'|| '-' || YEAR) between '2005-01-01' and '2009-01-01') I am getting the error No authorized routine named "DATE" of type "FUNCTION" having compatible arguments was found. 28dd20a3 JDBCException W org.hibernate.util.JDBCExceptionReporter SQL Error: -727, SQLState: 56098 28dd20a3 JDBCException E...
3
7343
by: lenygold via DBMonster.com | last post by:
Thank you very much SERGE for your help. I found example in Graeme Birchall COOKBOOK wich i think exactly what i need for SQL check in triggers: • User query joins to table function - sends DML or DDL statement to be executed. • Table function calls stored procedure - sends statement to be executed. • Stored procedure executes statement. • Stored procedure returns SQLCODE of statement to the table function.
0
9289
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9158
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9060
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9001
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6615
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5939
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4454
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4712
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2508
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.