DB2-L Digest - 12 Nov 2010 to 13 Nov 2010 (#2010-308)

Sheryl Larsen

DB2-L Digest - 12 Nov 2010 to 13 Nov 2010 (#2010-308)


DB2-L automatic digest system <[login to unmask email]> wrote:

>In V9 you cannot do that:
>
>Invalid use of scalar-fullselect. This reason
>code can be issued when the RETURN
>statement of an SQL function contains a
>scalar-fullselect, or a scalar-fullselect is passed
>as an argument on a CALL statement for a
>parameter that is defined as an input
>parameter (IN).
>
>
>Roy Boxwell
>SOFTWARE ENGINEERING GMBH
>-Product Development-
>Robert-Stolz-Straße 5
>40470 Düsseldorf/Germany
>Tel. +49 (0)211 96149-675
>Fax +49 (0)211 96149-32
>Email: [login to unmask email]
>http://www.seg.de
>
>Software Engineering GmbH
>Amtsgericht Düsseldorf, HRB 37894
>Geschäftsführung: Gerhard Schubert
>
>
>
>Sam Baugh <[login to unmask email]>
>Gesendet von: IDUG DB2-L <[login to unmask email]>
>11.11.2010 17:26
>Bitte antworten an
>IDUG DB2-L <[login to unmask email]>
>
>
>An
>[login to unmask email]
>Kopie
>
>Thema
>[DB2-L] z/OS DB2v9 NFM - UDF Help
>
>
>
>
>
>
>Does anyone have a sample UDF (SQL scalar) that performs an existence
>check that they would be willing to share? I am struggling with syntax
>(z/OS DB2v9 NFM) on what I am trying to create and the programmers have
>been creating these as external UDF’s. Thanks!
>
>This is what I am working with and current error message. If I take out
>the “BEGIN”, it doesn’t like the SELECT. I have been trying to base this
>on samples from Store Procedures, Triggers, UDF redbook, but it is for UDB
>iSeries.
>
>CREATE FUNCTION MI.INV_EXISTS ( PLOC CHAR(4), PNO CHAR(6) )
> RETURNS TIMESTAMP
> LANGUAGE SQL
> READS SQL DATA
> NO EXTERNAL ACTION
> NOT DETERMINISTIC
> BEGIN
> RETURN
> COALESCE((SELECT 'Y' FROM BROP.SHIPPER_HDR
> WHERE MI_LOC = PLOC AND ORDER_CTL_NO = PNO)
> ,'N')
> END;
>DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD BEGIN. TOKEN
> <END-OF-STATEMENT> SPECIFIC FENCED RETURN SOURCE PARAMETER WAS
> EXPECTED
>DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
>DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
>DSNT416I SQLERRD = 2 0 0 -1 145 506 SQL DIAGNOSTIC INFORMATION
>DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000'
> X'FFFFFFFF' X'00000091' X'000001FA' SQL DIAGNOSTIC
> INFORMATION
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>are not already an IDUG member, please register here.
>
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* If you are going to attend only one conference this year, this is it! *
>** The most DB2 technical sessions of any conference
>** Access IBM experts and developers
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
>
>That would be a good reason as to why my search for examples failed. At least it’s Friday now!
>
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roy Boxwell
>Sent: Friday, November 12, 2010 3:32 AM
>To: [login to unmask email]
>Subject: Re: [DB2-L] z/OS DB2v9 NFM - UDF Help
>
>
>In V9 you cannot do that:
>
>Invalid use of scalar-fullselect. This reason
>code can be issued when the RETURN
>statement of an SQL function contains a
>scalar-fullselect, or a scalar-fullselect is passed
>as an argument on a CALL statement for a
>parameter that is defined as an input
>parameter (IN).
>
>
>Roy Boxwell
>SOFTWARE ENGINEERING GMBH
>-Product Development-
>Robert-Stolz-Straße 5
>40470 Düsseldorf/Germany
>Tel. +49 (0)211 96149-675
>Fax +49 (0)211 96149-32
>Email: [login to unmask email]
>http://www.seg.de
>
>Software Engineering GmbH
>Amtsgericht Düsseldorf, HRB 37894
>Geschäftsführung: Gerhard Schubert
>
>Sam Baugh <[login to unmask email]>
>Gesendet von: IDUG DB2-L <[login to unmask email]>
>
>11.11.2010 17:26
>Bitte antworten an
>IDUG DB2-L <[login to unmask email]>
>
>
>An
>
>[login to unmask email]
>
>Kopie
>
>Thema
>
>[DB2-L] z/OS DB2v9 NFM - UDF Help
>
>
>
>
>
>
>
>Does anyone have a sample UDF (SQL scalar) that performs an existence check that they would be willing to share? I am struggling with syntax (z/OS DB2v9 NFM) on what I am trying to create and the programmers have been creating these as external UDF’s. Thanks!
>
>This is what I am working with and current error message. If I take out the “BEGIN”, it doesn’t like the SELECT. I have been trying to base this on samples from Store Procedures, Triggers, UDF redbook, but it is for UDB iSeries.
>
>CREATE FUNCTION MI.INV_EXISTS ( PLOC CHAR(4), PNO CHAR(6) )
> RETURNS TIMESTAMP
> LANGUAGE SQL
> READS SQL DATA
> NO EXTERNAL ACTION
> NOT DETERMINISTIC
> BEGIN
> RETURN
> COALESCE((SELECT 'Y' FROM BROP.SHIPPER_HDR
> WHERE MI_LOC = PLOC AND ORDER_CTL_NO = PNO)
> ,'N')
> END;
>DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD BEGIN. TOKEN
> <END-OF-STATEMENT> SPECIFIC FENCED RETURN SOURCE PARAMETER WAS
> EXPECTED
>DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
>DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
>DSNT416I SQLERRD = 2 0 0 -1 145 506 SQL DIAGNOSTIC INFORMATION
>DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000'
> X'FFFFFFFF' X'00000091' X'000001FA' SQL DIAGNOSTIC
> INFORMATION
>
>
>
>________________________________
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
>
>________________________________
>
>[ http://www.idug.org/images/stories/IDUG_Corporate/idug_conf_728.gif ] < http://www.idug.org/db2-conferences-list.html >
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* If you are going to attend only one conference this year, this is it! *
>** The most DB2 technical sessions of any conference
>** Access IBM experts and developers
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
>
>
>Hi list,
>
>This is in a 4-way Data Sharing in V9.
>In System Statistics , I add P_LOCKS (A)
>and consider P_LOCKS registrated in GBP (B).
>The "missing" P_LOCKS (A > B) are the Row level locks?
>
>
>I can't find where did they go !
>
>
>
>(A)
>
>DATA SHARING LOCKING QUANTITY
>
>
>--------------------------- --------
>
>GLOBAL CONTENTION RATE (%) 1.30
>
>
>P/L-LOCKS XES RATE (%) 1.48
>
>
>
>
>
>LOCK REQUESTS (P-LOCKS) 18272.8K
>
>
>UNLOCK REQUESTS (P-LOCKS) 17078.4K
>
>CHANGE REQUESTS (P-LOCKS) 77437.00
>
>
>
>
>SYNCH.XES - LOCK REQUESTS 6563.7K
>
>SYNCH.XES - CHANGE REQUESTS 244.8K
>
>SYNCH.XES - UNLOCK REQUESTS 72897.9K
>
>ASYNCH.XES - RESOURCES 6236.00
>
>
>
>
>
>SUSPENDS - IRLM GLOBAL CONT 599.9K
>
>SUSPENDS - XES GLOBAL CONT. 23544.00
>
>SUSPENDS - FALSE CONTENTION 422.8K
>
>INCOMPATIBLE RETAINED LOCK 0.00
>
>
>
>
>
>NOTIFY MESSAGES SENT 200.0K
>
>
>NOTIFY MESSAGES RECEIVED 498.4K
>
>
>P-LOCK/NOTIFY EXITS ENGINES 2000.00
>
>P-LCK/NFY EX.ENGINE UNAVAIL 0.00
>
>
>PSET/PART P-LCK NEGOTIATION 129.7K
>
>PAGE P-LOCK NEGOTIATION 382.3K
>
>
>OTHER P-LOCK NEGOTIATION 55834.00
>
>P-LOCK CHANGE DURING NEG. 532.2K
>
>
>
>
>
>
>(B)
>
>
>
>PAGE P-LOCK LOCK REQ 13977.1K
>
> SPACE MAP PAGES 3944.2K
>
>
> DATA PAGES 576.9K
>
>
> INDEX LEAF PAGES 9456.0K
>
>
>
>
>
>PAGE P-LOCK UNLOCK REQ 16347.8K
>
>
>
>
>PAGE P-LOCK LOCK SUSP 13827.1K
>
> SPACE MAP PAGES 3837.0K
>
>
> DATA PAGES 573.5K
>
>
> INDEX LEAF PAGES 9416.6K
>
>
>
>
>
>PAGE P-LOCK LOCK NEG 382.3K
>
> SPACE MAP PAGES 365.2K
>
>
> DATA PAGES 447.00
>
>
> INDEX LEAF PAGES 16643.00
>
>
>Thanks a lot,
>
>Jose
>
>
>
>
>
>
>
>
>
>
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* If you are going to attend only one conference this year, this is it! *
>** The most DB2 technical sessions of any conference
>** Access IBM experts and developers
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv