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

[Arnold Trembley]

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/


[LX-i]

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@thebelowdomain ~
~ _____ / \ | ~ 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

[Michael Mattias]

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

[The Boss]

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

[Michael Mattias]

"The Boss" <us****@No.Spam.Please.invalidwrote in message
news:46*********************@news.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

[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

In article <58***************************@FUSE.NET>,
CG <Ca**********************@MCGCG.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

[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**********************@MCGCG.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

[Arnold Trembley]

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/


[Su Nombre]

Asking Google about sqlcode302:

http://www.google.es/search?hl=es&q=sqlcode302&meta=

My firts hit:

http://www-1.ibm.com/support/docview...id=swg21159584

In owr shop, solution was increasing buffer size +1 and setting SQL string
length as 'buffer size' -3

Regards

Vicente

[Frank Swarbrick]

>>On 6/3/2007 at 12:31 AM, in message
<4f*******************@bgtnsc05-news.ops.worldnet.att.net>, Arnold
Trembley<ar*************@worldnet.att.netwrote:

>
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.

Hmm, my guess offhand is that your organization doesn't actually understand
the issue, which I assume is "SQL Injection":
http://en.wikipedia.org/wiki/SQL_injection

If dynamic SQL were really "forbidden" then I would guess that everyone
using JDBC would be in trouble, because JDBC uses only dynamic SQL, as far
as I know. (There is SQLJ which can be used in Java for static SQL, but I
don't think it's use is very prevalent.)

Frank

[Arnold Trembley]

Frank Swarbrick wrote:

>>>>On 6/3/2007 at 12:31 AM, in message

<4f*******************@bgtnsc05-news.ops.worldnet.att.net>, Arnold
Trembley<ar*************@worldnet.att.netwrote:

(snip)

>>So the short answer to your question is it's a step my organization is
taking to reduce its risk.

Hmm, my guess offhand is that your organization doesn't actually understand
the issue, which I assume is "SQL Injection":
http://en.wikipedia.org/wiki/SQL_injection

That could certainly be the case, although I think it is more likely
that *I* don't understand all the security issues. We're being told
that COBOL programs need to guard aginst "cross-site scripting", but
none of the COBOL applications I work with are web-enabled.

Instead, it's been suggested that we invalidate input fields that
contain special characters, such as % and +, for example.

>
If dynamic SQL were really "forbidden" then I would guess that everyone
using JDBC would be in trouble, because JDBC uses only dynamic SQL, as far
as I know. (There is SQLJ which can be used in Java for static SQL, but I
don't think it's use is very prevalent.)

Frank

There is some mainframe Java in my organization, but I have no
experience with JDBC. It's not in my COBOL applications. We've only
been using DB2 for about six or seven years and we haven't even tried
out dynamic SQL in COBOL yet.

With kindest regards,
--
http://arnold.trembley.home.att.net/


In article <R7********************@bgtnsc04-news.ops.worldnet.att.net>,
Arnold Trembley <ar*************@worldnet.att.netwrote:

[snip]

>There is some mainframe Java in my organization, but I have no
experience with JDBC. It's not in my COBOL applications. We've only
been using DB2 for about six or seven years and we haven't even tried
out dynamic SQL in COBOL yet.

Oh, I *cannot* resist... what an outmoded, outdated place that must be, Mr
Trembley! You only process, what... about a billion transactions a day,
across twenty-seven timezones or so?

DD

[Michael Russell]

Well done, Vicente.

and that's that.

Someone using up-to-date stuff (well, Google) and it's a hole
in one - that's funny.

Regards

Michael
Su Nombre wrote:

Asking Google about sqlcode302:

http://www.google.es/search?hl=es&q=sqlcode302&meta=

My firts hit:

http://www-1.ibm.com/support/docview...id=swg21159584

In owr shop, solution was increasing buffer size +1 and setting SQL string
length as 'buffer size' -3

Regards

Vicente

[Michael Mattias]

Su Nombre wrote:

>Asking Google about sqlcode302:

http://www.google.es/search?hl=es&q=sqlcode302&meta=

My firts hit:

http://www-1.ibm.com/support/docview...id=swg21159584

In owr shop, solution was increasing buffer size +1 and setting SQL
string
length as 'buffer size' -3

Well gee whiz, I guess I'd better stop assuming posters have tried the
obvious before asking here.

MCM

[Sri]

Thank you Michael . I changed the code and Its working in both V7 as well as
in V8
Thanks
Ravi
"Michael Mattias" <mm******@talsystems.comwrote in message
news:Xq*****************@newssvr22.news.prodigy.ne t...

>Su Nombre wrote:

>>Asking Google about sqlcode302:

http://www.google.es/search?hl=es&q=sqlcode302&meta=

My firts hit:

http://www-1.ibm.com/support/docview...id=swg21159584

In owr shop, solution was increasing buffer size +1 and setting SQL
string
length as 'buffer size' -3

Well gee whiz, I guess I'd better stop assuming posters have tried the
obvious before asking here.

MCM

[LX-i]

Arnold Trembley wrote:

>

Frank Swarbrick wrote:

>>>>On 6/3/2007 at 12:31 AM, in message

<4f*******************@bgtnsc05-news.ops.worldnet.att.net>, Arnold
Trembley<ar*************@worldnet.att.netwrote:

(snip)

>>So the short answer to your question is it's a step my organization
is taking to reduce its risk.

Hmm, my guess offhand is that your organization doesn't actually
understand
the issue, which I assume is "SQL Injection":
http://en.wikipedia.org/wiki/SQL_injection

That could certainly be the case, although I think it is more likely
that *I* don't understand all the security issues. We're being told
that COBOL programs need to guard aginst "cross-site scripting", but
none of the COBOL applications I work with are web-enabled.

Instead, it's been suggested that we invalidate input fields that
contain special characters, such as % and +, for example.

For SQL Injection, ' (apostrophe) and ; (semi-colon) are the ones that
can cause you problems... :)

There is some mainframe Java in my organization, but I have no
experience with JDBC. It's not in my COBOL applications. We've only
been using DB2 for about six or seven years and we haven't even tried
out dynamic SQL in COBOL yet.

On the Unisys mainframe (2200/ClearPath IX), just from a performance
angle, dynamic SQL costs significant overhead the first time it's used.
Granted, if you're executing the same dynamic statement 100 times or
more, it really doesn't matter. The numbers they gave in the manual for
performance were ratios. If a dynamic statement took 1.0 to process,
the same Static ESQL took 0.6. If the table definition had changed
since the program had been compiled, it did an on-the-fly compilation of
that statement; these took 0.65. Of course, the second and further
executions are the same.

One thing that the system I'm working on now does is keep all their
queries in XML files. Then, they pass a function name and parameter
array to the "DB Layer", and it handles all the database access. It's a
very cool system; if I had the time to do something like that for my
prior system, I'd be a hero. :) Load them up, PREPARE them as part of
system start-up - then boom! You don't have to recompile to change the
query, and you don't have to use dynamic SQL in your program!

Of course, Java makes that a little easier to do than COBOL, but it
could still be done. I'm thinking of a small procedural subprogram that
returns fields by name, and has a "move next" and "move prior" method as
well. It wouldn't be as pretty as

for (ReturnObject obj : aReturnObjects) {
sName = obj.getString("name");
sTitle = obj.getString("title");
iAge = obj.getInteger("age");
}

but it could be just as functional...

*Assuming that the "get data" puts the pointer at the first
*result, and sets ws-status if no results are found...
*Also keep in mind that this is straight COBOL 85, with no
*object extensions.
perform until ws-status = EOF

move "name" to ws-field
call "db-result-string" using ws-field ws-name
on exception go to uh-oh
end-call

move "title" to ws-field
call "db-result-string" using ws-field ws-title
on exception go to uh-oh
end-call

move "age" to ws-type
call "db-result-number" using ws-field ws-age
on exception go to uh-oh
end-call

call "db-next-result" using ws-status
on exception go to uh-oh
end-call

end-perform

This actually wouldn't be that hard to implement, as a working model.
What would be a bear is actually changing the code to use the new model.
IMO, it would be very cool and worth the effort, though.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelowdomain ~
~ _____ / \ | ~ 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

[Arnold Trembley]

do******@panix.com wrote:

In article <R7********************@bgtnsc04-news.ops.worldnet.att.net>,
Arnold Trembley <ar*************@worldnet.att.netwrote:

[snip]

>>There is some mainframe Java in my organization, but I have no
experience with JDBC. It's not in my COBOL applications. We've only
been using DB2 for about six or seven years and we haven't even tried
out dynamic SQL in COBOL yet.

Oh, I *cannot* resist... what an outmoded, outdated place that must be, Mr
Trembley! You only process, what... about a billion transactions a day,
across twenty-seven timezones or so?

DD

Actually, it's roughly between 50 and 100 million per day, but the 27
time zones is probably correct. :-)

With kindest regards,
--
http://arnold.trembley.home.att.net/


[Arnold Trembley]

LX-i wrote:

Arnold Trembley wrote:

>>

Frank Swarbrick wrote:

>>>>>On 6/3/2007 at 12:31 AM, in message
<4f*******************@bgtnsc05-news.ops.worldnet.att.net>, Arnold
Trembley<ar*************@worldnet.att.netwrote :

(snip)

So the short answer to your question is it's a step my organization
is taking to reduce its risk.

Hmm, my guess offhand is that your organization doesn't actually
understand
the issue, which I assume is "SQL Injection":
http://en.wikipedia.org/wiki/SQL_injection

That could certainly be the case, although I think it is more likely
that *I* don't understand all the security issues. We're being told
that COBOL programs need to guard aginst "cross-site scripting", but
none of the COBOL applications I work with are web-enabled.

Instead, it's been suggested that we invalidate input fields that
contain special characters, such as % and +, for example.

For SQL Injection, ' (apostrophe) and ; (semi-colon) are the ones that
can cause you problems... :)

Yes, I think those were also included in the list of "special
characters" that were risky.

>

>There is some mainframe Java in my organization, but I have no
experience with JDBC. It's not in my COBOL applications. We've only
been using DB2 for about six or seven years and we haven't even tried
out dynamic SQL in COBOL yet.

On the Unisys mainframe (2200/ClearPath IX), just from a performance
angle, dynamic SQL costs significant overhead the first time it's used.
Granted, if you're executing the same dynamic statement 100 times or
more, it really doesn't matter. The numbers they gave in the manual for
performance were ratios. If a dynamic statement took 1.0 to process,
the same Static ESQL took 0.6. If the table definition had changed
since the program had been compiled, it did an on-the-fly compilation of
that statement; these took 0.65. Of course, the second and further
executions are the same.

One thing that the system I'm working on now does is keep all their
queries in XML files. Then, they pass a function name and parameter
array to the "DB Layer", and it handles all the database access. It's a
very cool system; if I had the time to do something like that for my
prior system, I'd be a hero. :) Load them up, PREPARE them as part of
system start-up - then boom! You don't have to recompile to change the
query, and you don't have to use dynamic SQL in your program!

Of course, Java makes that a little easier to do than COBOL, but it
could still be done. I'm thinking of a small procedural subprogram that
returns fields by name, and has a "move next" and "move prior" method as
well. It wouldn't be as pretty as

for (ReturnObject obj : aReturnObjects) {
sName = obj.getString("name");
sTitle = obj.getString("title");
iAge = obj.getInteger("age");
}

but it could be just as functional...

*Assuming that the "get data" puts the pointer at the first
*result, and sets ws-status if no results are found...
*Also keep in mind that this is straight COBOL 85, with no
*object extensions.
perform until ws-status = EOF

move "name" to ws-field
call "db-result-string" using ws-field ws-name
on exception go to uh-oh
end-call

move "title" to ws-field
call "db-result-string" using ws-field ws-title
on exception go to uh-oh
end-call

move "age" to ws-type
call "db-result-number" using ws-field ws-age
on exception go to uh-oh
end-call

call "db-next-result" using ws-status
on exception go to uh-oh
end-call

end-perform

This actually wouldn't be that hard to implement, as a working model.
What would be a bear is actually changing the code to use the new model.
IMO, it would be very cool and worth the effort, though.

That's a very interesting idea, sort of a wrapper around the actual
SQL calls. I suppose it might also be done with DB2 stored
procedures. We have a few in the shop, but none in the applications I
support.

Thanks for sharing that.

With kindest regards,

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


[Pete Dashwood]

"Arnold Trembley" <ar*************@worldnet.att.netwrote in message
news:Qo********************@bgtnsc04-news.ops.worldnet.att.net...

>

LX-i wrote:

to the "DB Layer", and it handles all the database access. It's a

>very cool system; if I had the time to do something like that for my
prior system, I'd be a hero. :) Load them up, PREPARE them as part of
system start-up - then boom! You don't have to recompile to change the
query, and you don't have to use dynamic SQL in your program!

Why not simply use a data provider? Let the system use XML or RDB but the
interface to it would be the same.

I came across a C# web template the other day that has that option. You can
load up data from XML or SQL Server but they are seen as Data Providers and
the interface is identical. (Flick a switch on startup to decide DB or XML).
Add other Data Provider classes and the interface still remains the same...
really cool.

I'm not sure about encapsulating SQL into XML then dynamically setting the
SQL from that. It gives flexibility, certainly , but it also allows
maintenance of the SQL externally from the system. I know you see that as a
benefit, but there are downsides to it too...:-)

Every time you change the SQL you must regression test everything.

If the "DB Layer" was a (Data Provider) Class with Methods for each of the
queries, you wouldn't have the regression testing problem.

You could add and replace methods as required and everything else would
continue to function.

>Of course, Java makes that a little easier to do than COBOL, but it could
still be done. I'm thinking of a small procedural subprogram that
returns fields by name, and has a "move next" and "move prior" method as
well. It wouldn't be as pretty as

for (ReturnObject obj : aReturnObjects) {
sName = obj.getString("name");
sTitle = obj.getString("title");
iAge = obj.getInteger("age");
}

.... Or you could use a data reader object :-)

>>
but it could be just as functional...

*Assuming that the "get data" puts the pointer at the first
*result, and sets ws-status if no results are found...
*Also keep in mind that this is straight COBOL 85, with no
*object extensions.
perform until ws-status = EOF

move "name" to ws-field
call "db-result-string" using ws-field ws-name
on exception go to uh-oh
end-call

move "title" to ws-field
call "db-result-string" using ws-field ws-title
on exception go to uh-oh
end-call

move "age" to ws-type
call "db-result-number" using ws-field ws-age
on exception go to uh-oh
end-call

call "db-next-result" using ws-status
on exception go to uh-oh
end-call

end-perform

I'd like it better if you overloaded the DB call so it automatically decided
whether the field was a string or a number (you can get this from the DB
Schema; or, if you use a Table Adapter, it is returned automatically in
the column properties when you get the field.)

However, given that you want to stick to COBOL and not use OO... how about:

>perform until ws-status = EOF

move "name" to ws-field
call "db-get-result" using ws-field

returning ws-name

> on exception go to uh-oh
end-call

.... where you check the returned DB Host variable field for numeric or
alphanumeric before placing it in ws-name or whatever the returning field
for the call is.

( I don't like the condition being = EOF and the go to uh-oh either, but
we'll let those pass... :-)

Bottom line is... it's MUCH more elegant in C# (or Java).

>>
This actually wouldn't be that hard to implement, as a working model.
What would be a bear is actually changing the code to use the new model.
IMO, it would be very cool and worth the effort, though.

That's a very interesting idea, sort of a wrapper around the actual SQL
calls. I suppose it might also be done with DB2 stored procedures. We
have a few in the shop, but none in the applications I support.

Stored procedures would be a much better solution. The fields for the row
can be entered into host variables and that's it. You need a trigger for the
procedure, but it's fairly easy to contrive one. Not sure whether it is
possible to run a cursor from a stored proc but that has some interesting
possibilities too... :-)

Pete.

[LX-i]

Pete Dashwood wrote:

"Arnold Trembley" <ar*************@worldnet.att.netwrote in message
news:Qo********************@bgtnsc04-news.ops.worldnet.att.net...

>>
LX-i wrote:

to the "DB Layer", and it handles all the database access. It's a

>>very cool system; if I had the time to do something like that for my
prior system, I'd be a hero. :) Load them up, PREPARE them as part of
system start-up - then boom! You don't have to recompile to change the
query, and you don't have to use dynamic SQL in your program!

Why not simply use a data provider? Let the system use XML or RDB but the
interface to it would be the same.

I think that's what the "DB Layer" is - it could be swapped out for
whatever, and our "business functions" would still be named the same.

I'm not sure about encapsulating SQL into XML then dynamically setting the
SQL from that. It gives flexibility, certainly , but it also allows
maintenance of the SQL externally from the system. I know you see that as a
benefit, but there are downsides to it too...:-)

True - but my old system, the flexibility would *far* outweigh the
risks, IMO... We had changes that we needed to make, but politically,
we couldn't make them.

Every time you change the SQL you must regression test everything.

No - nowhere near "everything"... :) It's split into separate files,
so the changes are somewhat isolated.

If the "DB Layer" was a (Data Provider) Class with Methods for each of the
queries, you wouldn't have the regression testing problem.

You could add and replace methods as required and everything else would
continue to function.

I haven't gotten into it enough to know the exact implementation. Plus,
it's pretty much one of those things where you set it and forget it. It
returns an array of ReturnObject objects, which have the requisite
getString(), getInteger(), etc.

>>Of course, Java makes that a little easier to do than COBOL, but it could
still be done. I'm thinking of a small procedural subprogram that
returns fields by name, and has a "move next" and "move prior" method as
well. It wouldn't be as pretty as

for (ReturnObject obj : aReturnObjects) {
sName = obj.getString("name");
sTitle = obj.getString("title");
iAge = obj.getInteger("age");
}

... Or you could use a data reader object :-)

Would that be any less code?

for (DataRow dr in ds["my_table"]) {
sName = dr["name"].toString();
sTitle = dr["title"].toString();
iAge = Convert.ToInt32(dr["age"]);
}

>>but it could be just as functional...

*Assuming that the "get data" puts the pointer at the first
*result, and sets ws-status if no results are found...
*Also keep in mind that this is straight COBOL 85, with no
*object extensions.
perform until ws-status = EOF

move "name" to ws-field
call "db-result-string" using ws-field ws-name
on exception go to uh-oh
end-call

move "title" to ws-field
call "db-result-string" using ws-field ws-title
on exception go to uh-oh
end-call

move "age" to ws-type
call "db-result-number" using ws-field ws-age
on exception go to uh-oh
end-call

call "db-next-result" using ws-status
on exception go to uh-oh
end-call

end-perform

I'd like it better if you overloaded the DB call so it automatically decided
whether the field was a string or a number (you can get this from the DB
Schema; or, if you use a Table Adapter, it is returned automatically in
the column properties when you get the field.)

However, given that you want to stick to COBOL and not use OO... how about:

>>perform until ws-status = EOF

move "name" to ws-field
call "db-get-result" using ws-field

returning ws-name

>> on exception go to uh-oh
end-call

... where you check the returned DB Host variable field for numeric or
alphanumeric before placing it in ws-name or whatever the returning field
for the call is.

You could do that. I'm not sure whether the Unisys compiler supported
"returning" on the call statement. I've never seen it (but, of course,
that doesn't mean that it doesn't exist).

It could be done.

( I don't like the condition being = EOF and the go to uh-oh either, but
we'll let those pass... :-)

Think Q&D - those would be fleshed out later...

Bottom line is... it's MUCH more elegant in C# (or Java).

True - but, my idea is how to implement it in my former environment.
The one problem I have with my model is that it would only support one
open dataset at a time. Without being able to create another instance
of my data reader subprogram, I would have to put some kind of
hocus-pocus in it to keep track of multiple result sets, and then the
program would have to specify which one it wanted the "next" of.

(If I could do the object thing, it would be really easy...)

>>This actually wouldn't be that hard to implement, as a working model.
What would be a bear is actually changing the code to use the new model.
IMO, it would be very cool and worth the effort, though.

That's a very interesting idea, sort of a wrapper around the actual SQL
calls. I suppose it might also be done with DB2 stored procedures. We
have a few in the shop, but none in the applications I support.

Stored procedures would be a much better solution. The fields for the row
can be entered into host variables and that's it. You need a trigger for the
procedure, but it's fairly easy to contrive one. Not sure whether it is
possible to run a cursor from a stored proc but that has some interesting
possibilities too... :-)

In our shop, we figured out how to pass CLOBs across a linked database
in Oracle. I'm hoping to post the details on my tech blog when I get
the information pulled together. It uses stored procedures.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelowdomain ~
~ _____ / \ | ~ 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

[LX-i]

(pardon me while I talk to myself...)

LX-i wrote:

Pete Dashwood wrote:

>I'm not sure about encapsulating SQL into XML then dynamically setting
the SQL from that. It gives flexibility, certainly , but it also
allows maintenance of the SQL externally from the system. I know you
see that as a benefit, but there are downsides to it too...:-)

True - but my old system, the flexibility would *far* outweigh the
risks, IMO... We had changes that we needed to make, but politically,
we couldn't make them.

I forgot to mention that, in our current environment, these XML files
are as secure as the rest of our code, and are deployed compressed into
a WAR file. (Not that it's impossible to change things there, it's just
a bit more challenging.)
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelowdomain ~
~ _____ / \ | ~ 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