Cursor Usage In Assembler

Jim Thomas

Cursor Usage In Assembler

Hello All,

I am very new to DB2 and am trying to work on updating an old program.

The existing code has a SELECT RELEASE ..... and was getting a -811 .. I

was trying to change this to a SELECT RELEASE ... WHERE NAME = :GRANTEE .. but

that too ended with -811's and -313's .. changing it to a CURSOR was suggested .. so,

I coded up a few lines (alongside the DECLARE's for the other CURSORs) with two

DECLAREs .. essentially the similar and contains the above SELECT but one, pointing to

SYSIBM.SYSPLAN and just the WHERE NAME = clause as above and the second, pointing to SYSIBM.SYSPACKAGES alongside the WHERE NAME with AND GRANTEE = :GRANTEE.

Obviously, this is followed by the OPEN .. FETCH and CLOSE .. but .. when I run the

batch test job ... I keep getting a -804, followed by several -313's .. much like a loop..

I just am not sure what I'm doing wrong ..

 

I'd be happy to provide more specifics if needed or if I can but I just

don't, at the moment, know what would be needed..

 

Any pointers, directions or advice would be appreciated.

 

Jim Thomas

bernd oppolzer

Cursor Usage In Assembler
(in response to Jim Thomas)
Am 25.02.2017 um 17:20 schrieb Jim Thomas:
>
> Hello All,
>
> I am very new to DB2 and am trying to work on updating an old program.
>
> The existing code has a SELECT RELEASE ..... and was getting a -811 .. I
>

it would help us if you showed the whole statement including the
table accessed; from below I guess it may be a catalog table,
for example SYSIBM.SYSPLAN or ... SYSPACKAGE, and RELEASE is
simply a column name there.

> was trying to change this to a SELECT RELEASE ... WHERE NAME =
> :GRANTEE .. but
>
> that too ended with -811's and -313's ..
>
first of all: do you expect more than one result at this place?
Then you have to put more WHERE conditions to the SELECT
to make the access unique; coding a Cursor Select will not be
the correct decision.

If you expect more then one result, you should decide what to
do with that multiple results. The application has to handle them;
until now, apparently, it was not able to handle them, so this could
be a major redesign of the application.


>
> changing it to a CURSOR was suggested .. so,
>

maybe not the correct decision, too short sighted ...

> I coded up a few lines (alongside the DECLARE's for the other CURSORs)
> with two
>

other cursors? How do there SQLs look? Maybe it would help us, if
we knew about them too ...

> DECLAREs .. essentially the similar and contains the above SELECT but
> one, pointing to
>
> SYSIBM.SYSPLAN and just the WHERE NAME = clause as above and the
> second, pointing to SYSIBM.SYSPACKAGES alongside the WHERE NAME with
> AND GRANTEE = :GRANTEE.
>
> Obviously, this is followed by the OPEN .. FETCH and CLOSE .. but ..
> when I run the
>
> batch test job ... I keep getting a -804, followed by several -313's
> .. much like a loop..
>

-804 and -313 are total different errors,
nothing to do with the original -811;
wrong parameters or wrong count of parameters.

> I just am not sure what I'm doing wrong ..
>
> I'd be happy to provide more specifics if needed or if I can but I just
> don't, at the moment, know what would be needed..
>

IMO, this is not related to ASSEMBLER;
the original SQLs and the definitions of the Hostvariables would
help much - the one that gave the -811 originally; maybe there is
an easy way to make the result unique, by adding some WHERE
conditions, but that's not only a question of technique, but we need
to understand what the SQL is supposed to do.


> Any pointers, directions or advice would be appreciated.
>
> Jim Thomas
>
>

Kind regards

Bernd

bernd oppolzer

Cursor Usage In Assembler
(in response to Jim Thomas)

Am 25.02.2017 um 17:42 schrieb Bernd Oppolzer:
>
>> was trying to change this to a SELECT RELEASE ... WHERE NAME =
>> :GRANTEE ..
>> but that too ended with -811's and -313's ..
>>
> first of all: do you expect more than one result at this place?
> (Correction:)
> If not, you have to put more WHERE conditions to the SELECT
> to make the access unique; coding a Cursor Select will not be
> the correct decision.
>
> If you expect more then one result, you should decide what to
> do with that multiple results. The application has to handle them;
> until now, apparently, it was not able to handle them, so this could
> be a major redesign of the application.
>

Jim Thomas

Cursor Usage In Assembler
(in response to bernd oppolzer)
Hello Bernd,

Thank you for your response ..

First .. the two (previous) SELECT are as below ..

SELECT RELEASE FROM SYSIBM.SYSPLAN WHERE NAME = :GRANTEE
and
SELECT RELEASE FROM SYSIBM.SYSPACKAGE WHERE NAME = :GRANTEE AND COLLID = :COLLID

Either one are selected based on a PARM value from the batch test job.

Again .. I am a DB2 newbie .. while I do not understand why .. all the code does is
to check the INTO part (host-variable ??) and is if it's a blank .. if so, it moves a 'C' into it .. but..
it does not UPDATE the column .. it does this for the batch job only (I have no idea why) ..

The suggestion to use a CURSOR was triggered from the -811's that it was initially getting.

All I know is that the SELECT returns multiple rows.. hence the -811..

Yes .. not only was this program written to not handle anything .. it's a (no offence meant to
the author), IMHO, very poorly written program .. that said .. again, IMHO, I am not versed in
DB2 (yet) hence .. my cry for help.

The DECLARE replacements were essentially coded (around the SELECTs) just to get around the
-811's ...

Hope I made a tiny bit of sense... my apologies ..


Kind Regards,

Jim Thomas

-----Original Message-----
From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Saturday, February 25, 2017 10:42
To: [login to unmask email]
Subject: [DB2-L] - RE: Cursor Usage In Assembler

Am 25.02.2017 um 17:20 schrieb Jim Thomas:
>
> Hello All,
>
> I am very new to DB2 and am trying to work on updating an old program.
>
> The existing code has a SELECT RELEASE ..... and was getting a -811 ..
> I
>

it would help us if you showed the whole statement including the table accessed; from below I guess it may be a catalog table, for example SYSIBM.SYSPLAN or ... SYSPACKAGE, and RELEASE is simply a column name there.

> was trying to change this to a SELECT RELEASE ... WHERE NAME =
> :GRANTEE .. but
>
> that too ended with -811's and -313's ..
>
first of all: do you expect more than one result at this place?
Then you have to put more WHERE conditions to the SELECT to make the access unique; coding a Cursor Select will not be the correct decision.

If you expect more then one result, you should decide what to do with that multiple results. The application has to handle them; until now, apparently, it was not able to handle them, so this could be a major redesign of the application.


>
> changing it to a CURSOR was suggested .. so,
>

maybe not the correct decision, too short sighted ...

> I coded up a few lines (alongside the DECLARE's for the other CURSORs)
> with two
>

other cursors? How do there SQLs look? Maybe it would help us, if we knew about them too ...

> DECLAREs .. essentially the similar and contains the above SELECT but
> one, pointing to
>
> SYSIBM.SYSPLAN and just the WHERE NAME = clause as above and the
> second, pointing to SYSIBM.SYSPACKAGES alongside the WHERE NAME with
> AND GRANTEE = :GRANTEE.
>
> Obviously, this is followed by the OPEN .. FETCH and CLOSE .. but ..
> when I run the
>
> batch test job ... I keep getting a -804, followed by several -313's
> .. much like a loop..
>

-804 and -313 are total different errors, nothing to do with the original -811; wrong parameters or wrong count of parameters.

> I just am not sure what I'm doing wrong ..
>
> I'd be happy to provide more specifics if needed or if I can but I
> just don't, at the moment, know what would be needed..
>

IMO, this is not related to ASSEMBLER;
the original SQLs and the definitions of the Hostvariables would help much - the one that gave the -811 originally; maybe there is an easy way to make the result unique, by adding some WHERE conditions, but that's not only a question of technique, but we need to understand what the SQL is supposed to do.


> Any pointers, directions or advice would be appreciated.
>
> Jim Thomas
>
>

Kind regards

Bernd

-----End Original Message-----




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

Jim Thomas

Cursor Usage In Assembler
(in response to bernd oppolzer)
Yep ... As w/my previous response ... all I'm doing, if the RELEASE is a space, making it a
'C' ..

Kind Regards,

Jim Thomas


-----Original Message-----
From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Saturday, February 25, 2017 10:46
To: [login to unmask email]
Subject: [DB2-L] - RE: Cursor Usage In Assembler


Am 25.02.2017 um 17:42 schrieb Bernd Oppolzer:
>
>> was trying to change this to a SELECT RELEASE ... WHERE NAME =
>> :GRANTEE ..
>> but that too ended with -811's and -313's ..
>>
> first of all: do you expect more than one result at this place?
> (Correction:)
> If not, you have to put more WHERE conditions to the SELECT to make
> the access unique; coding a Cursor Select will not be the correct
> decision.
>
> If you expect more then one result, you should decide what to do with
> that multiple results. The application has to handle them; until now,
> apparently, it was not able to handle them, so this could be a major
> redesign of the application.
>

-----End Original Message-----




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

bernd oppolzer

Cursor Usage In Assembler
(in response to Jim Thomas)
Hello Jim,

RELEASE is a BIND Parameter of Plans and Packages, which can take
several values;

Let's see, what a blank in the RELEASE column means;
the Appendix in the SQL Reference on SYSIBM.SYSPLAN only
talks about 2 possible values: C = RELEASE(COMMIT);
D = RELEASE(DEALLOCATE). So Blank should not occur (maybe in
prior releases).

On the other hand, SYSPACKAGE has a RELEASE parameter, too;
the book says: if RELEASE in SYSPACKAGE is blank, then the
value is taken from the plan executing the package.

So the program obviously reads SYSIBM.SYSPLAN (and SYSIBM.SYSPACKAGE)
for a given plan or package, and if it finds a blank in the RELEASE Bind
Parameter
for the plan (which should not occur) or for the package (which is
possible),
it sets a C there (= COMMIT) for further processing.

I hope that this makes sense ...

My next question (if I were in charge) would be:

why does the SELECT find two plans in SYSIBM.SYSPLAN or SYSIBM.SYSPACKAGE
for a given plan name?

IMO, the SELECT on SYSIBM.SYSPLAN cannot find two columns for
a given NAME, because there is a unique index on NAME - that is,
there is only one plan for a given name. Please check, where the -811
SQLCODE comes from; IMO it must be the SELECT on SYSIBM.SYSPACKAGE.

There we have the primary index on the columns LOCATION / COLLID / NAME
/ VERSION

so there is a chance that you will get duplicate packages due to the
usage of VERSION, for example. (By using the VERSION parameter on
Package BINDs, you will get different VERSIONs of the same package
at the same time in SYSIBM.SYSPACKAGE; at execution time DB2 picks
the right one).

Please check if this is the case.

If so, you have several choices:

a) remove the older (unneeded) packages - talk to the package owner

b) refine your SQL, so that only the newest package will be selected

c) read the VERSION id from the job parameter, too, and add the
WHERE condition to the SELECT ... FROM SYSPACKAGE

all three methods will remove the -811; no cursor needed.

If you want your program to handle all packages with the given
name and collection, regardless of the version id, you will have
to declare a cursor, open it and extend to logic of your program
to fetch the results in a loop and do the work which is now done
once in the loop for every record fetched. After that, the cursor
should be closed.

HTH,
kind regards

Bernd



Am 25.02.2017 um 18:18 schrieb Jim:
> Hello Bernd,
>
> Thank you for your response ..
>
> First .. the two (previous) SELECT are as below ..
>
> SELECT RELEASE FROM SYSIBM.SYSPLAN WHERE NAME = :GRANTEE
> and
> SELECT RELEASE FROM SYSIBM.SYSPACKAGE WHERE NAME = :GRANTEE AND COLLID = :COLLID
>
> Either one are selected based on a PARM value from the batch test job.
>
> Again .. I am a DB2 newbie .. while I do not understand why .. all the code does is
> to check the INTO part (host-variable ??) and is if it's a blank .. if so, it moves a 'C' into it .. but..
> it does not UPDATE the column .. it does this for the batch job only (I have no idea why) ..
>
> The suggestion to use a CURSOR was triggered from the -811's that it was initially getting.
>
> All I know is that the SELECT returns multiple rows.. hence the -811..
>
> Yes .. not only was this program written to not handle anything .. it's a (no offence meant to
> the author), IMHO, very poorly written program .. that said .. again, IMHO, I am not versed in
> DB2 (yet) hence .. my cry for help.
>
> The DECLARE replacements were essentially coded (around the SELECTs) just to get around the
> -811's ...
>
> Hope I made a tiny bit of sense... my apologies ..
>
>
> Kind Regards,
>
> Jim Thomas
>
> -----Original Message-----
> From: Bernd Oppolzer [mailto:[login to unmask email]
> Sent: Saturday, February 25, 2017 10:42
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Cursor Usage In Assembler
>
> Am 25.02.2017 um 17:20 schrieb Jim Thomas:
>> Hello All,
>>
>> I am very new to DB2 and am trying to work on updating an old program.
>>
>> The existing code has a SELECT RELEASE ..... and was getting a -811 ..
>> I
>>
> it would help us if you showed the whole statement including the table accessed; from below I guess it may be a catalog table, for example SYSIBM.SYSPLAN or ... SYSPACKAGE, and RELEASE is simply a column name there.
>
>> was trying to change this to a SELECT RELEASE ... WHERE NAME =
>> :GRANTEE .. but
>>
>> that too ended with -811's and -313's ..
>>
> first of all: do you expect more than one result at this place?
> Then you have to put more WHERE conditions to the SELECT to make the access unique; coding a Cursor Select will not be the correct decision.
>
> If you expect more then one result, you should decide what to do with that multiple results. The application has to handle them; until now, apparently, it was not able to handle them, so this could be a major redesign of the application.
>
>
>> changing it to a CURSOR was suggested .. so,
>>
> maybe not the correct decision, too short sighted ...
>
>> I coded up a few lines (alongside the DECLARE's for the other CURSORs)
>> with two
>>
> other cursors? How do there SQLs look? Maybe it would help us, if we knew about them too ...
>
>> DECLAREs .. essentially the similar and contains the above SELECT but
>> one, pointing to
>>
>> SYSIBM.SYSPLAN and just the WHERE NAME = clause as above and the
>> second, pointing to SYSIBM.SYSPACKAGES alongside the WHERE NAME with
>> AND GRANTEE = :GRANTEE.
>>
>> Obviously, this is followed by the OPEN .. FETCH and CLOSE .. but ..
>> when I run the
>>
>> batch test job ... I keep getting a -804, followed by several -313's
>> .. much like a loop..
>>
> -804 and -313 are total different errors, nothing to do with the original -811; wrong parameters or wrong count of parameters.
>
>> I just am not sure what I'm doing wrong ..
>>
>> I'd be happy to provide more specifics if needed or if I can but I
>> just don't, at the moment, know what would be needed..
>>
> IMO, this is not related to ASSEMBLER;
> the original SQLs and the definitions of the Hostvariables would help much - the one that gave the -811 originally; maybe there is an easy way to make the result unique, by adding some WHERE conditions, but that's not only a question of technique, but we need to understand what the SQL is supposed to do.
>
>
>> Any pointers, directions or advice would be appreciated.
>>
>> Jim Thomas
>>
>>
> Kind regards
>
> Bernd
>
> -----End Original Message-----
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.
>
> -----End Original Message-----
>
>

Jim Thomas

Cursor Usage In Assembler
(in response to bernd oppolzer)
Hello Bernd,

Once again .. thank you for your helpful response ..

Please bear w/me while I digest your responses, hit the books again and umm..
scratch me head for a while .. :-)

Again .. thank you.

Kind Regards,

Jim Thomas

-----Original Message-----
From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Saturday, February 25, 2017 12:44
To: [login to unmask email]
Subject: [DB2-L] - RE: Cursor Usage In Assembler

Hello Jim,

RELEASE is a BIND Parameter of Plans and Packages, which can take several values;

Let's see, what a blank in the RELEASE column means; the Appendix in the SQL Reference on SYSIBM.SYSPLAN only talks about 2 possible values: C = RELEASE(COMMIT); D = RELEASE(DEALLOCATE). So Blank should not occur (maybe in prior releases).

On the other hand, SYSPACKAGE has a RELEASE parameter, too; the book says: if RELEASE in SYSPACKAGE is blank, then the value is taken from the plan executing the package.

So the program obviously reads SYSIBM.SYSPLAN (and SYSIBM.SYSPACKAGE) for a given plan or package, and if it finds a blank in the RELEASE Bind Parameter for the plan (which should not occur) or for the package (which is possible), it sets a C there (= COMMIT) for further processing.

I hope that this makes sense ...

My next question (if I were in charge) would be:

why does the SELECT find two plans in SYSIBM.SYSPLAN or SYSIBM.SYSPACKAGE for a given plan name?

IMO, the SELECT on SYSIBM.SYSPLAN cannot find two columns for a given NAME, because there is a unique index on NAME - that is, there is only one plan for a given name. Please check, where the -811 SQLCODE comes from; IMO it must be the SELECT on SYSIBM.SYSPACKAGE.

There we have the primary index on the columns LOCATION / COLLID / NAME / VERSION

so there is a chance that you will get duplicate packages due to the usage of VERSION, for example. (By using the VERSION parameter on Package BINDs, you will get different VERSIONs of the same package at the same time in SYSIBM.SYSPACKAGE; at execution time DB2 picks the right one).

Please check if this is the case.

If so, you have several choices:

a) remove the older (unneeded) packages - talk to the package owner

b) refine your SQL, so that only the newest package will be selected

c) read the VERSION id from the job parameter, too, and add the WHERE condition to the SELECT ... FROM SYSPACKAGE

all three methods will remove the -811; no cursor needed.

If you want your program to handle all packages with the given name and collection, regardless of the version id, you will have to declare a cursor, open it and extend to logic of your program to fetch the results in a loop and do the work which is now done once in the loop for every record fetched. After that, the cursor should be closed.

HTH,
kind regards

Bernd



Am 25.02.2017 um 18:18 schrieb Jim:
> Hello Bernd,
>
> Thank you for your response ..
>
> First .. the two (previous) SELECT are as below ..
>
> SELECT RELEASE FROM SYSIBM.SYSPLAN WHERE NAME = :GRANTEE and SELECT
> RELEASE FROM SYSIBM.SYSPACKAGE WHERE NAME = :GRANTEE AND COLLID =
> :COLLID
>
> Either one are selected based on a PARM value from the batch test job.
>
> Again .. I am a DB2 newbie .. while I do not understand why .. all the
> code does is to check the INTO part (host-variable ??) and is if it's a blank .. if so, it moves a 'C' into it .. but..
> it does not UPDATE the column .. it does this for the batch job only (I have no idea why) ..
>
> The suggestion to use a CURSOR was triggered from the -811's that it was initially getting.
>
> All I know is that the SELECT returns multiple rows.. hence the -811..
>
> Yes .. not only was this program written to not handle anything ..
> it's a (no offence meant to the author), IMHO, very poorly written
> program .. that said .. again, IMHO, I am not versed in
> DB2 (yet) hence .. my cry for help.
>
> The DECLARE replacements were essentially coded (around the SELECTs)
> just to get around the -811's ...
>
> Hope I made a tiny bit of sense... my apologies ..
>
>
> Kind Regards,
>
> Jim Thomas
>
> -----Original Message-----
> From: Bernd Oppolzer [mailto:[login to unmask email]
> Sent: Saturday, February 25, 2017 10:42
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Cursor Usage In Assembler
>
> Am 25.02.2017 um 17:20 schrieb Jim Thomas:
>> Hello All,
>>
>> I am very new to DB2 and am trying to work on updating an old program.
>>
>> The existing code has a SELECT RELEASE ..... and was getting a -811 ..
>> I
>>
> it would help us if you showed the whole statement including the table accessed; from below I guess it may be a catalog table, for example SYSIBM.SYSPLAN or ... SYSPACKAGE, and RELEASE is simply a column name there.
>
>> was trying to change this to a SELECT RELEASE ... WHERE NAME =
>> :GRANTEE .. but
>>
>> that too ended with -811's and -313's ..
>>
> first of all: do you expect more than one result at this place?
> Then you have to put more WHERE conditions to the SELECT to make the access unique; coding a Cursor Select will not be the correct decision.
>
> If you expect more then one result, you should decide what to do with that multiple results. The application has to handle them; until now, apparently, it was not able to handle them, so this could be a major redesign of the application.
>
>
>> changing it to a CURSOR was suggested .. so,
>>
> maybe not the correct decision, too short sighted ...
>
>> I coded up a few lines (alongside the DECLARE's for the other
>> CURSORs) with two
>>
> other cursors? How do there SQLs look? Maybe it would help us, if we knew about them too ...
>
>> DECLAREs .. essentially the similar and contains the above SELECT but
>> one, pointing to
>>
>> SYSIBM.SYSPLAN and just the WHERE NAME = clause as above and the
>> second, pointing to SYSIBM.SYSPACKAGES alongside the WHERE NAME with
>> AND GRANTEE = :GRANTEE.
>>
>> Obviously, this is followed by the OPEN .. FETCH and CLOSE .. but ..
>> when I run the
>>
>> batch test job ... I keep getting a -804, followed by several -313's
>> .. much like a loop..
>>
> -804 and -313 are total different errors, nothing to do with the original -811; wrong parameters or wrong count of parameters.
>
>> I just am not sure what I'm doing wrong ..
>>
>> I'd be happy to provide more specifics if needed or if I can but I
>> just don't, at the moment, know what would be needed..
>>
> IMO, this is not related to ASSEMBLER; the original SQLs and the
> definitions of the Hostvariables would help much - the one that gave the -811 originally; maybe there is an easy way to make the result unique, by adding some WHERE conditions, but that's not only a question of technique, but we need to understand what the SQL is supposed to do.
>
>
>> Any pointers, directions or advice would be appreciated.
>>
>> Jim Thomas
>>
>>
> Kind regards
>
> Bernd
>
> -----End Original Message-----
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.
>
> -----End Original Message-----
>
>

-----End Original Message-----




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

Jim Thomas

Cursor Usage In Assembler
(in response to bernd oppolzer)
Hello Bernd ...

My apologies for my delay in responding ... got pulled off to multiple other priorities ..

I ended up going back to the original SELECT ... for SYSPLAN, changing the NAME = to a specific
PLAN and for SYSPACKAGES, changing the WHERE for the PLAN name AND requiring a CONTOKEN
(provided by the user) ..

The above actions, resolved all of the errors and or abends I faced ..

Thank you so very much for taking the time to explain this and point me in the right direction.

If you ever need MVS (non DB2) assistance ... I'd be more than happy to point you in the right
direction ..

Again .. thank you so very much ..


Kind Regards,

Jim Thomas

-----Original Message-----
From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Saturday, February 25, 2017 12:44
To: [login to unmask email]
Subject: [DB2-L] - RE: Cursor Usage In Assembler

Hello Jim,

RELEASE is a BIND Parameter of Plans and Packages, which can take several values;

Let's see, what a blank in the RELEASE column means; the Appendix in the SQL Reference on SYSIBM.SYSPLAN only talks about 2 possible values: C = RELEASE(COMMIT); D = RELEASE(DEALLOCATE). So Blank should not occur (maybe in prior releases).

On the other hand, SYSPACKAGE has a RELEASE parameter, too; the book says: if RELEASE in SYSPACKAGE is blank, then the value is taken from the plan executing the package.

So the program obviously reads SYSIBM.SYSPLAN (and SYSIBM.SYSPACKAGE) for a given plan or package, and if it finds a blank in the RELEASE Bind Parameter for the plan (which should not occur) or for the package (which is possible), it sets a C there (= COMMIT) for further processing.

I hope that this makes sense ...

My next question (if I were in charge) would be:

why does the SELECT find two plans in SYSIBM.SYSPLAN or SYSIBM.SYSPACKAGE for a given plan name?

IMO, the SELECT on SYSIBM.SYSPLAN cannot find two columns for a given NAME, because there is a unique index on NAME - that is, there is only one plan for a given name. Please check, where the -811 SQLCODE comes from; IMO it must be the SELECT on SYSIBM.SYSPACKAGE.

There we have the primary index on the columns LOCATION / COLLID / NAME / VERSION

so there is a chance that you will get duplicate packages due to the usage of VERSION, for example. (By using the VERSION parameter on Package BINDs, you will get different VERSIONs of the same package at the same time in SYSIBM.SYSPACKAGE; at execution time DB2 picks the right one).

Please check if this is the case.

If so, you have several choices:

a) remove the older (unneeded) packages - talk to the package owner

b) refine your SQL, so that only the newest package will be selected

c) read the VERSION id from the job parameter, too, and add the WHERE condition to the SELECT ... FROM SYSPACKAGE

all three methods will remove the -811; no cursor needed.

If you want your program to handle all packages with the given name and collection, regardless of the version id, you will have to declare a cursor, open it and extend to logic of your program to fetch the results in a loop and do the work which is now done once in the loop for every record fetched. After that, the cursor should be closed.

HTH,
kind regards

Bernd



Am 25.02.2017 um 18:18 schrieb Jim:
> Hello Bernd,
>
> Thank you for your response ..
>
> First .. the two (previous) SELECT are as below ..
>
> SELECT RELEASE FROM SYSIBM.SYSPLAN WHERE NAME = :GRANTEE and SELECT
> RELEASE FROM SYSIBM.SYSPACKAGE WHERE NAME = :GRANTEE AND COLLID =
> :COLLID
>
> Either one are selected based on a PARM value from the batch test job.
>
> Again .. I am a DB2 newbie .. while I do not understand why .. all the
> code does is to check the INTO part (host-variable ??) and is if it's a blank .. if so, it moves a 'C' into it .. but..
> it does not UPDATE the column .. it does this for the batch job only (I have no idea why) ..
>
> The suggestion to use a CURSOR was triggered from the -811's that it was initially getting.
>
> All I know is that the SELECT returns multiple rows.. hence the -811..
>
> Yes .. not only was this program written to not handle anything ..
> it's a (no offence meant to the author), IMHO, very poorly written
> program .. that said .. again, IMHO, I am not versed in
> DB2 (yet) hence .. my cry for help.
>
> The DECLARE replacements were essentially coded (around the SELECTs)
> just to get around the -811's ...
>
> Hope I made a tiny bit of sense... my apologies ..
>
>
> Kind Regards,
>
> Jim Thomas
>
> -----Original Message-----
> From: Bernd Oppolzer [mailto:[login to unmask email]
> Sent: Saturday, February 25, 2017 10:42
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Cursor Usage In Assembler
>
> Am 25.02.2017 um 17:20 schrieb Jim Thomas:
>> Hello All,
>>
>> I am very new to DB2 and am trying to work on updating an old program.
>>
>> The existing code has a SELECT RELEASE ..... and was getting a -811 ..
>> I
>>
> it would help us if you showed the whole statement including the table accessed; from below I guess it may be a catalog table, for example SYSIBM.SYSPLAN or ... SYSPACKAGE, and RELEASE is simply a column name there.
>
>> was trying to change this to a SELECT RELEASE ... WHERE NAME =
>> :GRANTEE .. but
>>
>> that too ended with -811's and -313's ..
>>
> first of all: do you expect more than one result at this place?
> Then you have to put more WHERE conditions to the SELECT to make the access unique; coding a Cursor Select will not be the correct decision.
>
> If you expect more then one result, you should decide what to do with that multiple results. The application has to handle them; until now, apparently, it was not able to handle them, so this could be a major redesign of the application.
>
>
>> changing it to a CURSOR was suggested .. so,
>>
> maybe not the correct decision, too short sighted ...
>
>> I coded up a few lines (alongside the DECLARE's for the other
>> CURSORs) with two
>>
> other cursors? How do there SQLs look? Maybe it would help us, if we knew about them too ...
>
>> DECLAREs .. essentially the similar and contains the above SELECT but
>> one, pointing to
>>
>> SYSIBM.SYSPLAN and just the WHERE NAME = clause as above and the
>> second, pointing to SYSIBM.SYSPACKAGES alongside the WHERE NAME with
>> AND GRANTEE = :GRANTEE.
>>
>> Obviously, this is followed by the OPEN .. FETCH and CLOSE .. but ..
>> when I run the
>>
>> batch test job ... I keep getting a -804, followed by several -313's
>> .. much like a loop..
>>
> -804 and -313 are total different errors, nothing to do with the original -811; wrong parameters or wrong count of parameters.
>
>> I just am not sure what I'm doing wrong ..
>>
>> I'd be happy to provide more specifics if needed or if I can but I
>> just don't, at the moment, know what would be needed..
>>
> IMO, this is not related to ASSEMBLER; the original SQLs and the
> definitions of the Hostvariables would help much - the one that gave the -811 originally; maybe there is an easy way to make the result unique, by adding some WHERE conditions, but that's not only a question of technique, but we need to understand what the SQL is supposed to do.
>
>
>> Any pointers, directions or advice would be appreciated.
>>
>> Jim Thomas
>>
>>
> Kind regards
>
> Bernd
>
> -----End Original Message-----
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.
>
> -----End Original Message-----
>
>

-----End Original Message-----




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4749/13836 - Release Date: 01/25/17 Internal Virus Database is out of date.