Accessing SQLCA within a SQL procedure

Sanjay (Exchange) Jain

Accessing SQLCA within a SQL procedure
Hi all

Is it possible to access SQLCA within a SQL stored procedure ? Everything I
have seen/read so far points to 'NO'. But perhaps there is a way ?

DSNTIAR and DNSACCMG are not an option. Subroutines can not be called from a
SQL procedure etc. etc.

TIA

Sanjay Jain



-----Original Message-----
From: Tina Hilton [mailto:[login to unmask email]
Sent: Thursday, November 21, 2002 11:40 AM
To: [login to unmask email]
Subject: Re: declared global temporary tables


Thanks to everyone for the answers. I have a question about your latest
comment, Myron. How do you find the culprit when your one tablespace goes
into extents?

Tina








***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************



Myron Miller

Re: Accessing SQLCA within a SQL procedure
(in response to Sanjay (Exchange) Jain)
Not directly. However, with V7.2 of Db2 Connect and V6 of DB2, some of the
fields in SQLCA can be accessed via set commands. And I read somewhere that
more of them (if not all of them ) will be available when V8 of DB2 connect
comes (or is that Vnext).
--- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> Hi all
>
> Is it possible to access SQLCA within a SQL stored procedure ? Everything I
> have seen/read so far points to 'NO'. But perhaps there is a way ?
>
> DSNTIAR and DNSACCMG are not an option. Subroutines can not be called from a
> SQL procedure etc. etc.
>
> TIA
>
> Sanjay Jain
>
>
>
> -----Original Message-----
> From: Tina Hilton [mailto:[login to unmask email]
> Sent: Thursday, November 21, 2002 11:40 AM
> To: [login to unmask email]
> Subject: Re: declared global temporary tables
>
>
> Thanks to everyone for the answers. I have a question about your latest
> comment, Myron. How do you find the culprit when your one tablespace goes
> into extents?
>
> Tina
>
>
>
>
>
>
>
>
> ***********************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Sanjay (Exchange) Jain

Re: Accessing SQLCA in a SQL proc
(in response to Myron Miller)
David,

Thanks for replying back. Do you have any sample of how SQLCA is defined in
a "SQL" stored proc ? I can do it in a COBOL stored proc, but have not been
able to define it in SQL/PL stored proc. I added following declarations in
the proc, taking a cue from DSNACCMG

DECLARE SQLCAID CHAR(8);
DECLARE SQLCABC SMALLINT;
DECLARE SQLCODE INTEGER;
DECLARE SQLERRML SMALLINT;
DECLARE SQLERRMC CHAR(70);
DECLARE SQLERRP CHAR(8);
DECLARE SQLERRD0 INTEGER;
DECLARE SQLERRD1 INTEGER;
DECLARE SQLERRD2 INTEGER;
DECLARE SQLERRD3 INTEGER;
DECLARE SQLERRD4 INTEGER;
DECLARE SQLERRD5 INTEGER;
DECLARE SQLWARN CHAR(11);
DECLARE SQLSTATE CHAR(5);

But only SQLCODE and SQLSTATE variables are populated by generated 'C' code
when an SQL is executed in the proc. Something like

SQLCODE (variable defined in proc) = sqlca.sqlcode;
memcpy(SQLSTATE(variable defined in proc), sqlca.sqlstate,5);

There is no "memcpy(SQLERRMC(variable defined in proc),
sqlca.sqlerrmc,70);" statement in the 'C' code.

Again, could you tell me how to define SQLCA in SQL stored procedure. ?

Regards

Sanjay Jain

-----Original Message-----
From: David Cohn [mailto:[login to unmask email]
Sent: Thursday, November 21, 2002 1:22 PM
To: [login to unmask email]
Subject:


Hi Sanjay,
You can define an SQLCA within the stored procedure. It will be local to
the stored procedure and will reflect all DB2 calls by the stored procedure.
The SQLCA of the calling program will reflect the status of the call to the
stored procedure.
Hope this helps,

David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development


****************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************



Sanjay (Exchange) Jain

Re: Accessing SQLCA within a SQL procedure
(in response to Sanjay (Exchange) Jain)
Myron,

Thank you for replying back. We are DB2 connect 7.2 fixpack 7. "SET"
statement does not work for any SQLCA fields. I get "Undefined or unusable
host variable" error which makes sense as I don't know how to define SQLCA
in a SQL proc.
"INCLUDE SQLCA" is not allowed. "DECLARE"ing each field of SQLCA
automatically populates(the generated 'C' code has statements for) SQLCODE
and SQLSTATE fields only. No SET statement is required. I am interested in
accessing SQLERRMC field as it contains additional SQLCODE for a failed
trigger caused by an INSERT statement in the SQL proc.

I am wondering if SQLCA is available in java stored proc via some java
class.

Regards

Sanjay Jain



-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, November 21, 2002 2:00 PM
To: [login to unmask email]
Subject: Re: Accessing SQLCA within a SQL procedure


Not directly. However, with V7.2 of Db2 Connect and V6 of DB2, some of the
fields in SQLCA can be accessed via set commands. And I read somewhere that
more of them (if not all of them ) will be available when V8 of DB2 connect
comes (or is that Vnext).
--- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> Hi all
>
> Is it possible to access SQLCA within a SQL stored procedure ? Everything
I
> have seen/read so far points to 'NO'. But perhaps there is a way ?
>
> DSNTIAR and DNSACCMG are not an option. Subroutines can not be called from
a
> SQL procedure etc. etc.
>
> TIA
>
> Sanjay Jain
>
>
>
> -----Original Message-----
> From: Tina Hilton [mailto:[login to unmask email]
> Sent: Thursday, November 21, 2002 11:40 AM
> To: [login to unmask email]
> Subject: Re: declared global temporary tables
>
>
> Thanks to everyone for the answers. I have a question about your latest
> comment, Myron. How do you find the culprit when your one tablespace goes
> into extents?
>
> Tina
>
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
> ***********************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com







****************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************



David Cohn

Re: Accessing SQLCA in a SQL proc
(in response to Sanjay (Exchange) Jain)
Hi Sanjay,
I'm not a C programmer, so I can't help you with the C code. Someone
else will have to pick up on that question.
Sorry,
David Cohn Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Jain, Sanjay (Exchange)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, November 21, 2002 11:45 AM
Subject: Re: Accessing SQLCA in a SQL proc


> David,
>
> Thanks for replying back. Do you have any sample of how SQLCA is defined
in
> a "SQL" stored proc ? I can do it in a COBOL stored proc, but have not
been
> able to define it in SQL/PL stored proc. I added following declarations in
> the proc, taking a cue from DSNACCMG
>
> DECLARE SQLCAID CHAR(8);
> DECLARE SQLCABC SMALLINT;
> DECLARE SQLCODE INTEGER;
> DECLARE SQLERRML SMALLINT;
> DECLARE SQLERRMC CHAR(70);
> DECLARE SQLERRP CHAR(8);
> DECLARE SQLERRD0 INTEGER;
> DECLARE SQLERRD1 INTEGER;
> DECLARE SQLERRD2 INTEGER;
> DECLARE SQLERRD3 INTEGER;
> DECLARE SQLERRD4 INTEGER;
> DECLARE SQLERRD5 INTEGER;
> DECLARE SQLWARN CHAR(11);
> DECLARE SQLSTATE CHAR(5);
>
> But only SQLCODE and SQLSTATE variables are populated by generated 'C'
code
> when an SQL is executed in the proc. Something like
>
> SQLCODE (variable defined in proc) = sqlca.sqlcode;
> memcpy(SQLSTATE(variable defined in proc), sqlca.sqlstate,5);
>
> There is no "memcpy(SQLERRMC(variable defined in proc),
> sqlca.sqlerrmc,70);" statement in the 'C' code.
>
> Again, could you tell me how to define SQLCA in SQL stored procedure. ?
>
> Regards
>
> Sanjay Jain
>
> -----Original Message-----
> From: David Cohn [mailto:[login to unmask email]
> Sent: Thursday, November 21, 2002 1:22 PM
> To: [login to unmask email]
> Subject:
>
>
> Hi Sanjay,
> You can define an SQLCA within the stored procedure. It will be local
to
> the stored procedure and will reflect all DB2 calls by the stored
procedure.
> The SQLCA of the calling program will reflect the status of the call to
the
> stored procedure.
> Hope this helps,
>
> David Cohn
> Senior Systems Advisor
> Themis Training
> IBM Certified Solutions Expert:
> DB2 V7.1 Database Administration
> DB2 V7.1 Family Application Development
>
>
> ****************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>


>



Roger Miller

Re: Accessing SQLCA in a SQL proc
(in response to David Cohn)
In a C program (or COBOL or C++ or PL/I or assembler), the easy way to get
a SQLCA is EXEC SQL INCLUDE SQLCA

In the Application Programming and SQL Guide, this is spelled out for each
language in chapter 9 (V7). The SQLCA is described in more detail in the
SQL Reference appendix C.

Roger Miller



Myron Miller

Re: Accessing SQLCA in a SQL proc
(in response to Roger Miller)
Roger,
That's all well and good. But in a SQL Stored procedure the original
language is SQL. It is translated to C but we as programmers don't have any
control over that. It doesn't start as C.

The question was how does a person in an SQL Stored procedure access the SQLCA?

Myron
--- Roger Miller <[login to unmask email]> wrote:
> In a C program (or COBOL or C++ or PL/I or assembler), the easy way to get
> a SQLCA is EXEC SQL INCLUDE SQLCA
>
> In the Application Programming and SQL Guide, this is spelled out for each
> language in chapter 9 (V7). The SQLCA is described in more detail in the
> SQL Reference appendix C.
>
> Roger Miller
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Sanjay (Exchange) Jain

Re: Accessing SQLCA in a SQL proc
(in response to Myron Miller)
David,

My question is about SQL stored procedure, not 'C' code that is generated
when the SQL proc is build in SPB. I am curious if there is anyone out there
in DB2 world who has developed SQL stored procedure and has been able to use
SQLCA in it.

Thanks

Sanjay Jain

-----Original Message-----
From: David Cohn [mailto:[login to unmask email]
Sent: Thursday, November 21, 2002 5:20 PM
To: [login to unmask email]
Subject: Re: Accessing SQLCA in a SQL proc


Hi Sanjay,
I'm not a C programmer, so I can't help you with the C code. Someone
else will have to pick up on that question.
Sorry,
David Cohn Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Jain, Sanjay (Exchange)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, November 21, 2002 11:45 AM
Subject: Re: Accessing SQLCA in a SQL proc


> David,
>
> Thanks for replying back. Do you have any sample of how SQLCA is defined
in
> a "SQL" stored proc ? I can do it in a COBOL stored proc, but have not
been
> able to define it in SQL/PL stored proc. I added following declarations in
> the proc, taking a cue from DSNACCMG
>
> DECLARE SQLCAID CHAR(8);
> DECLARE SQLCABC SMALLINT;
> DECLARE SQLCODE INTEGER;
> DECLARE SQLERRML SMALLINT;
> DECLARE SQLERRMC CHAR(70);
> DECLARE SQLERRP CHAR(8);
> DECLARE SQLERRD0 INTEGER;
> DECLARE SQLERRD1 INTEGER;
> DECLARE SQLERRD2 INTEGER;
> DECLARE SQLERRD3 INTEGER;
> DECLARE SQLERRD4 INTEGER;
> DECLARE SQLERRD5 INTEGER;
> DECLARE SQLWARN CHAR(11);
> DECLARE SQLSTATE CHAR(5);
>
> But only SQLCODE and SQLSTATE variables are populated by generated 'C'
code
> when an SQL is executed in the proc. Something like
>
> SQLCODE (variable defined in proc) = sqlca.sqlcode;
> memcpy(SQLSTATE(variable defined in proc), sqlca.sqlstate,5);
>
> There is no "memcpy(SQLERRMC(variable defined in proc),
> sqlca.sqlerrmc,70);" statement in the 'C' code.
>
> Again, could you tell me how to define SQLCA in SQL stored procedure. ?
>
> Regards
>
> Sanjay Jain
>
> -----Original Message-----
> From: David Cohn [mailto:[login to unmask email]
> Sent: Thursday, November 21, 2002 1:22 PM
> To: [login to unmask email]
> Subject:
>
>
> Hi Sanjay,
> You can define an SQLCA within the stored procedure. It will be local
to
> the stored procedure and will reflect all DB2 calls by the stored
procedure.
> The SQLCA of the calling program will reflect the status of the call to
the
> stored procedure.
> Hope this helps,
>
> David Cohn
> Senior Systems Advisor
> Themis Training
> IBM Certified Solutions Expert:
> DB2 V7.1 Database Administration
> DB2 V7.1 Family Application Development
>
>
> ****************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>


>







***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************



Roger Miller

Re: Accessing SQLCA in a SQL proc
(in response to Sanjay (Exchange) Jain)
The best technique is documented in the redbook, Cross-Platform DB2 Stored
Procedures: Building and Debugging, SG24-5485-01. Look up GET DIAGNOSTICS
there or in the SQL Reference chapter on SQL Procedure statements.

You can get the SQLCODE and SQLSTATE variables directly, but the primary
technique is the GET DIAGNOSTICS statement, not the SQLCA. When we get
long names and some additional complexity, I expect almost everyone to
switch from the SQLCA to GET DIAGNOSTICS.

Roger Miller



Myron Miller

Re: Accessing SQLCA within a SQL procedure
(in response to Roger Miller)
Sanjay,
Did you see Roger Miller's reply on using Get Diagnostics. This is the way
to get the SQLCA data without including SQLCA.

Myron
--- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> Myron,
>
> Thank you for replying back. We are DB2 connect 7.2 fixpack 7. "SET"
> statement does not work for any SQLCA fields. I get "Undefined or unusable
> host variable" error which makes sense as I don't know how to define SQLCA
> in a SQL proc.
> "INCLUDE SQLCA" is not allowed. "DECLARE"ing each field of SQLCA
> automatically populates(the generated 'C' code has statements for) SQLCODE
> and SQLSTATE fields only. No SET statement is required. I am interested in
> accessing SQLERRMC field as it contains additional SQLCODE for a failed
> trigger caused by an INSERT statement in the SQL proc.
>
> I am wondering if SQLCA is available in java stored proc via some java
> class.
>
> Regards
>
> Sanjay Jain
>
>
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, November 21, 2002 2:00 PM
> To: [login to unmask email]
> Subject: Re: Accessing SQLCA within a SQL procedure
>
>
> Not directly. However, with V7.2 of Db2 Connect and V6 of DB2, some of the
> fields in SQLCA can be accessed via set commands. And I read somewhere that
> more of them (if not all of them ) will be available when V8 of DB2 connect
> comes (or is that Vnext).
> --- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> > Hi all
> >
> > Is it possible to access SQLCA within a SQL stored procedure ? Everything
> I
> > have seen/read so far points to 'NO'. But perhaps there is a way ?
> >
> > DSNTIAR and DNSACCMG are not an option. Subroutines can not be called from
> a
> > SQL procedure etc. etc.
> >
> > TIA
> >
> > Sanjay Jain
> >
> >
> >
> > -----Original Message-----
> > From: Tina Hilton [mailto:[login to unmask email]
> > Sent: Thursday, November 21, 2002 11:40 AM
> > To: [login to unmask email]
> > Subject: Re: declared global temporary tables
> >
> >
> > Thanks to everyone for the answers. I have a question about your latest
> > comment, Myron. How do you find the culprit when your one tablespace goes
> > into extents?
> >
> > Tina
> >
> >
> >
> >
> the
> > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> be
> >
> >
> >
> > ***********************************************************************
> > Bear Stearns is not responsible for any recommendation, solicitation,
> > offer or agreement or any information about any transaction, customer
> > account or account activity contained in this communication.
> > ***********************************************************************
> >
> >
> >
> the
> > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> be
> >
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
>
>
>
>
>
>
> ****************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Sanjay (Exchange) Jain

Re: Accessing SQLCA in a SQL proc
(in response to Myron Miller)
Thanks Roger. I will download the red book and take a look.

-----Original Message-----
From: Roger Miller [mailto:[login to unmask email]
Sent: Saturday, November 23, 2002 1:41 PM
To: [login to unmask email]
Subject: Re: Accessing SQLCA in a SQL proc


The best technique is documented in the redbook, Cross-Platform DB2 Stored
Procedures: Building and Debugging, SG24-5485-01. Look up GET DIAGNOSTICS
there or in the SQL Reference chapter on SQL Procedure statements.

You can get the SQLCODE and SQLSTATE variables directly, but the primary
technique is the GET DIAGNOSTICS statement, not the SQLCA. When we get
long names and some additional complexity, I expect almost everyone to
switch from the SQLCA to GET DIAGNOSTICS.

Roger Miller







****************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************



Sanjay (Exchange) Jain

Re: Accessing SQLCA within a SQL procedure
(in response to Sanjay (Exchange) Jain)
Yeah. I did. Thanks Myron.

GET DIAGNOSTICS has a MESSAGE_TEXT field which is suppose to provide
SQLERRMC. However MESSAGE_TEXT is not supported on z/OS. The only
information that GET DIAGNOSTIC will provide for SQL proc on mainframe is
ROW_COUNT. i.e. # of rows affected by previous SQL. There are 2 more fields
that are available on other platforms. These are RETURN_STATUS and
MESSAGE_TEXT. There is a table in chapter 3 in the redbook that lists all
the SQL statements and the platform they are supported on (in a SQL proc).
Most of the time this table has a 'Y' or 'N' indicator for a SQL statement
and its various options. However, for "GET DIAGNOSTICS MESSAGE_TEXT", there
is a "blank" in the os/390 column. Whatever that means.

This brings up another question for Roger. Will MESSAGE_TEXT field be
available via GET DIAGNOSTICS statement in future or are there in fixes
available currently to make it work on main frame ?

Scott, I gave it a shot anyway as described in an example in the redbook. I
got compilation error. MESSAGE_TEXT is not recognized, only valid option is
ROW_COUNT. This answers your question as well.

Regards

Sanjay Jain

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Monday, November 25, 2002 9:04 AM
To: [login to unmask email]
Subject: Re: Accessing SQLCA within a SQL procedure


Sanjay,
Did you see Roger Miller's reply on using Get Diagnostics. This is the
way
to get the SQLCA data without including SQLCA.

Myron
--- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> Myron,
>
> Thank you for replying back. We are DB2 connect 7.2 fixpack 7. "SET"
> statement does not work for any SQLCA fields. I get "Undefined or unusable
> host variable" error which makes sense as I don't know how to define SQLCA
> in a SQL proc.
> "INCLUDE SQLCA" is not allowed. "DECLARE"ing each field of SQLCA
> automatically populates(the generated 'C' code has statements for) SQLCODE
> and SQLSTATE fields only. No SET statement is required. I am interested in
> accessing SQLERRMC field as it contains additional SQLCODE for a failed
> trigger caused by an INSERT statement in the SQL proc.
>
> I am wondering if SQLCA is available in java stored proc via some java
> class.
>
> Regards
>
> Sanjay Jain
>
>
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, November 21, 2002 2:00 PM
> To: [login to unmask email]
> Subject: Re: Accessing SQLCA within a SQL procedure
>
>
> Not directly. However, with V7.2 of Db2 Connect and V6 of DB2, some of
the
> fields in SQLCA can be accessed via set commands. And I read somewhere
that
> more of them (if not all of them ) will be available when V8 of DB2
connect
> comes (or is that Vnext).
> --- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> > Hi all
> >
> > Is it possible to access SQLCA within a SQL stored procedure ?
Everything
> I
> > have seen/read so far points to 'NO'. But perhaps there is a way ?
> >
> > DSNTIAR and DNSACCMG are not an option. Subroutines can not be called
from
> a
> > SQL procedure etc. etc.
> >
> > TIA
> >
> > Sanjay Jain
> >
> >
> >
> > -----Original Message-----
> > From: Tina Hilton [mailto:[login to unmask email]
> > Sent: Thursday, November 21, 2002 11:40 AM
> > To: [login to unmask email]
> > Subject: Re: declared global temporary tables
> >
> >
> > Thanks to everyone for the answers. I have a question about your latest
> > comment, Myron. How do you find the culprit when your one tablespace
goes
> > into extents?
> >
> > Tina
> >
> >
> >
> >
> the
> > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> be
> >
> >
> >
> > ***********************************************************************
> > Bear Stearns is not responsible for any recommendation, solicitation,
> > offer or agreement or any information about any transaction, customer
> > account or account activity contained in this communication.
> > ***********************************************************************
> >
> >
> >
> the
> > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> be
> >
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
> ****************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com







***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************



Myron Miller

Re: Accessing SQLCA within a SQL procedure
(in response to Sanjay (Exchange) Jain)
I can't speak for Roger. But I've definitely heard and read that in some
future release of DB2 (Vnext?) and DB2 Connect V8 that the entire SQLCA will
become available this way. When, who knows and he's not telling. :=)
--- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> Yeah. I did. Thanks Myron.
>
> GET DIAGNOSTICS has a MESSAGE_TEXT field which is suppose to provide
> SQLERRMC. However MESSAGE_TEXT is not supported on z/OS. The only
> information that GET DIAGNOSTIC will provide for SQL proc on mainframe is
> ROW_COUNT. i.e. # of rows affected by previous SQL. There are 2 more fields
> that are available on other platforms. These are RETURN_STATUS and
> MESSAGE_TEXT. There is a table in chapter 3 in the redbook that lists all
> the SQL statements and the platform they are supported on (in a SQL proc).
> Most of the time this table has a 'Y' or 'N' indicator for a SQL statement
> and its various options. However, for "GET DIAGNOSTICS MESSAGE_TEXT", there
> is a "blank" in the os/390 column. Whatever that means.
>
> This brings up another question for Roger. Will MESSAGE_TEXT field be
> available via GET DIAGNOSTICS statement in future or are there in fixes
> available currently to make it work on main frame ?
>
> Scott, I gave it a shot anyway as described in an example in the redbook. I
> got compilation error. MESSAGE_TEXT is not recognized, only valid option is
> ROW_COUNT. This answers your question as well.
>
> Regards
>
> Sanjay Jain
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Monday, November 25, 2002 9:04 AM
> To: [login to unmask email]
> Subject: Re: Accessing SQLCA within a SQL procedure
>
>
> Sanjay,
> Did you see Roger Miller's reply on using Get Diagnostics. This is the
> way
> to get the SQLCA data without including SQLCA.
>
> Myron
> --- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> > Myron,
> >
> > Thank you for replying back. We are DB2 connect 7.2 fixpack 7. "SET"
> > statement does not work for any SQLCA fields. I get "Undefined or unusable
> > host variable" error which makes sense as I don't know how to define SQLCA
> > in a SQL proc.
> > "INCLUDE SQLCA" is not allowed. "DECLARE"ing each field of SQLCA
> > automatically populates(the generated 'C' code has statements for) SQLCODE
> > and SQLSTATE fields only. No SET statement is required. I am interested in
> > accessing SQLERRMC field as it contains additional SQLCODE for a failed
> > trigger caused by an INSERT statement in the SQL proc.
> >
> > I am wondering if SQLCA is available in java stored proc via some java
> > class.
> >
> > Regards
> >
> > Sanjay Jain
> >
> >
> >
> > -----Original Message-----
> > From: Myron Miller [mailto:[login to unmask email]
> > Sent: Thursday, November 21, 2002 2:00 PM
> > To: [login to unmask email]
> > Subject: Re: Accessing SQLCA within a SQL procedure
> >
> >
> > Not directly. However, with V7.2 of Db2 Connect and V6 of DB2, some of
> the
> > fields in SQLCA can be accessed via set commands. And I read somewhere
> that
> > more of them (if not all of them ) will be available when V8 of DB2
> connect
> > comes (or is that Vnext).
> > --- "Jain, Sanjay (Exchange)" <[login to unmask email]> wrote:
> > > Hi all
> > >
> > > Is it possible to access SQLCA within a SQL stored procedure ?
> Everything
> > I
> > > have seen/read so far points to 'NO'. But perhaps there is a way ?
> > >
> > > DSNTIAR and DNSACCMG are not an option. Subroutines can not be called
> from
> > a
> > > SQL procedure etc. etc.
> > >
> > > TIA
> > >
> > > Sanjay Jain
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Tina Hilton [mailto:[login to unmask email]
> > > Sent: Thursday, November 21, 2002 11:40 AM
> > > To: [login to unmask email]
> > > Subject: Re: declared global temporary tables
> > >
> > >
> > > Thanks to everyone for the answers. I have a question about your latest
> > > comment, Myron. How do you find the culprit when your one tablespace
> goes
> > > into extents?
> > >
> > > Tina
> > >
> > >
> > >
> > >
> > the
> > > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> > be
> > >
> > >
> > >
> > > ***********************************************************************
> > > Bear Stearns is not responsible for any recommendation, solicitation,
> > > offer or agreement or any information about any transaction, customer
> > > account or account activity contained in this communication.
> > > ***********************************************************************
> > >
> > >
> > >
> > the
> > > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> > be
> > >
> >
> >
> > __________________________________________________
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > http://mailplus.yahoo.com
> >
> >
> >
> the
> > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> be
> >
> >
> >
> > ****************************************************************
> > Bear Stearns is not responsible for any recommendation, solicitation,
> > offer or agreement or any information about any transaction, customer
> > account or account activity contained in this communication.
> > ***********************************************************************
> >
> >
> >
> the
> > DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
> be
> >
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
>
>
>
>
>
>
> ***********************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
> ***********************************************************************
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Roger Miller

Re: Accessing SQLCA within a SQL procedure
(in response to Myron Miller)
I guess I've been working on Vnext too long. If you check on the web
presentations, you'll see some improvements in the plan. For example, the
latest one is currently the Technology Highlights, page 19 in Acrobat or
foil 37. The V7 books were just updated in October, and they are the same.

Here are some web pointers for the information, just in case you don't have
them handy. Sometimes I think that the web information is one of our best-
kept secrets. Please try out the web and especially the Support page:

IBM DB2 Electronic Support: A web-based repository of information for you:
answers to questions, hints & tips, technical presentations, books, and
more.

ibm.com/software/db2zos Click on Support

Were you unable to get to recent conferences and still need the
information? Are you looking for additional support? Have you had problems
finding answers to your questions? Would you like to see hints and tips for
some of the techniques you use? Do you want to get recent technical
presentations? Do you need to be able to search for DB2 Redbooks more
effectively? Do you need white papers? Get on the web now. Click on the
Support entry on your DB2 web page. We have added many hundreds of answers
to frequently asked questions and hints and tips documents.

full URL for DB2 for z/OS page
http://www.ibm.com/software/data/db2/os390/index.html

presentations page: This is where to get the presentations below.

http://www.ibm.com/software/data/db2/os390/presentations.html

Migrate to DB2 for z/OS & OS/390 Version 7
This presentation has some updated information that may be useful
in rolling out V7. For data sharing also see
http://www.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/Flash10194

News: DB2 for z/OS
http://www.ibm.com/software/data/db2/os390/techdocs2/z01.pdf
Overview of what we expect for Vnext
http://www.ibm.com/software/data/db2/os390/techdocs/DMDB12.pdf
ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/tec
hdocs/db2vnext.pdf
Introduction to Vnext Catalog Changes
ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/tec
hdocs/vnextcatalog.pdf
DB2 Developer Domain articles
http://www7b.boulder.ibm.com/dmdd/
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0209cotner/020
9cotner.html
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0206miller/020
6miller.html

direct URLs for Support pages
http://www.ibm.com/software/data/db2/os390/support.html
http://www.ibm.com/software/data/db2/os390/osupport.html

V7 Books: Click on Library on the left. Most of the key books
are just updated - October 2002.
http://www.ibm.com/software/data/db2/os390/library.html
http://www.ibm.com/software/data/db2/os390/v7books.html



Theo van Westrienen

Re: Accessing SQLCA within a SQL procedure
(in response to Roger Miller)
Thanks for all the info and the references to the IBM site. But: I still
don't understand whether it is (in v7) or will be possible (in
vNext) to access SQLCA or at least the message text from within a SQL
Procedure.
Earlier this year (july) there was a thread called "Stored Procedure Error
Checking", in which there was a reference to a few PTFs (a.o. PQ56323). We
installed these, but I still cannot find a way to access the error message
text.

Can someone help me out on this?

regards, Theo van Westrienen



James Campbell

Re: Accessing SQLCA within a SQL procedure
(in response to Theo van Westrienen)
I have a little experience with SQL Language Stored Procedures, but
here goes:

V7 - not officially
Vnext - supposedly GET DIAGNOSTICS will do the trick.

However, and here the hacker in me comes out of its shell, there is a
technique that I believe (conceptually it should, but like a battle
plan in war, I don't know if it will survive contact with reality)
there is a way of getting the SQLCA - even in V6.

The starting points to recognise are
- your SQL Language SP is converted to a C language program
- the C language code is available - a suitable amendment to DSNHSQL
or DSNTPSMP will enable a user written program to take the code and
make whatever amendments are desirable. WARNING this is not a
documented programming interface, you will probably receive a "you
keep the pieces" response if there are any problems (in fact I'll
probably get one for just suggesting this).

I would suggest making these changes to the code immediately before
it is compiled. The way that WHENEVER/CONDITION code is implemented
means that if you use the un-precompiled C code you may have problems
finding a suitable point to insert your own code.

So, if you make it a rule to have an output parameter called, say,
LAST_SQLCA (as a bit string), read through the code looking for calls
to DSNHLI and immediately before each add a memcpy to copy the SQLCA
to LAST_SQLCA. If you wanted to get fancy you could call DSNTIAR to
format the SQLCA and return the formatted version, enabling remote
clients to understand it.

Remember that there are a number of things that can change that will
break this technique. So be prepared to have to rewrite your SQL
code.

James Campbell

On 30 Dec 2002 at 6:11, Theo van Westrienen wrote:

> Thanks for all the info and the references to the IBM site. But: I still
> don't understand whether it is (in v7) or will be possible (in
> vNext) to access SQLCA or at least the message text from within a SQL
> Procedure.
> Earlier this year (july) there was a thread called "Stored Procedure Error
> Checking", in which there was a reference to a few PTFs (a.o. PQ56323). We
> installed these, but I still cannot find a way to access the error message
> text.
>
> Can someone help me out on this?
>
> regards, Theo van Westrienen
>
>
>