版次注意事項


38.3 Chapter 4. Functions

|38.3.1 Enabling the New Functions and Procedures

|Version 7 FixPaks deliver new SQL built-in scalar functions. Refer to the SQL Reference updates for a description of these |new functions. The new functions are not automatically enabled on each database |when the database server code is upgraded to the new service level. To enable |these new functions, the system administrator must issue the command db2updv7, specifying each database at the server. This command makes |an entry in the database that ensures that database objects created prior |to executing this command use existing function signatures that may match |the new function signatures.

|For information on enabling the MQSeries functions (those defined |in the MQDB2 schema), see MQSeries.

38.3.2 Scalar Functions

|38.3.2.1 ABS or ABSVAL

|>>-+-ABS----+--(expression)------------------------------------><
|   '-ABSVAL-'
| 

|The schema is SYSIBM.

|This function was first available in FixPak 2 of Version 7.1.

|Note:
The SYSFUN version of the ABS (or ABSVAL) function continues to be available. |

|Returns the absolute value of the argument.

|The argument is an expression that returns a value of any built-in numeric |data type.

|The result of the function has the same data type and length attribute |as the argument. If the argument can be null or the database is configured |with DFT_SQLMATHWARN set to yes, then the result can be null; if the argument |is null, the result is the null value.

|For example:

|ABS(-51234)

|returns an INTEGER with a value of 51234.

|38.3.2.2 DECRYPT_BIN and DECRYPT_CHAR

| | | |
|>>-+-DECRYPT_BIN--+--------------------------------------------->
|   '-DECRYPT_CHAR-'
| 
|>----(--encrypted-data--+--------------------------------+---)--><
|                        '-,--password-string-expression--'
| 

|The schema is SYSIBM.

|This function was first available in FixPak 3 of Version 7.1.

|The DECRYPT_BIN and DECRYPT_CHAR functions return a value that is |the result of decrypting encrypted-data. The password |used for decryption is either the password-string-expression |value or the ENCRYPTION PASSWORD value (as assigned using the SET ENCRYPTION |PASSWORD statement). The DECRYPT_BIN and DECRYPT_CHAR functions can only decrypt |values that were encrypted using the ENCRYPT function (SQLSTATE 428FE).

|encrypted-data
|An expression that returns a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA |value that is a complete, encrypted data string that was encrypted using the |ENCRYPT function.

|password-string-expression
|An expression that returns a CHAR or VARCHAR value with at least 6 bytes |and no more than 127 bytes (SQLSTATE 428FC). This should be the same password |used to encrypt the data or decryption will result in an error (SQLSTATE 428FD). |If the value of the password argument is null or not provided, the data will |be decrypted using the ENCRYPTION PASSWORD value, which must have been set |for the session (SQLSTATE 51039). |

|The result of the DECRYPT_BIN function is VARCHAR FOR BIT DATA. The |result of the DECRYPT_CHAR function is VARCHAR. If the encrypted-data included a hint, the hint is not returned by the function. The |length attribute of the result is the length attribute of the data type of encrypted-data minus 8 bytes. The actual length of the value returned |by the function will match the length of the original string that was encrypted. |If the encrypted-data includes bytes beyond the encrypted |string, these bytes are not returned by the function. If the first argument |can be null, the result can be null; if the first argument is null, the result |is the null value.

|If the data is decrypted on a different system using a code page other |than the code page in which the encryption took place, it is possible that |expansion may occur when converting the decrypted value to the database code |page. In such situations, the encrypted-data value should |be cast to a VARCHAR string with a larger number of bytes.

|Also see 38.3.2.3, ENCRYPT and 38.3.2.4, GETHINT for additional |information on using this function.

|Examples:

|Example 1: This example uses the ENCRYPTION PASSWORD |value to hold the encryption password.

|   SET ENCRYPTION PASSWORD = 'Ben123';
|   INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832');
|   SELECT DECRYPT_CHAR(SSN)
|      FROM SSN;

|The value returned is '289-46-8832'.

|Example 2: This example explicitly passes the encryption |password.

|      SELECT DECRYPT_CHAR(SSN,'Ben123')
|      FROM SSN;

|The value returned is '289-46-8832'.

|38.3.2.3 ENCRYPT

| | | |
|>>-ENCRYPT------------------------------------------------------>
| 
|>----(--data-string-expression--+----------------------------------------------------------------+---)->
|                                '-,--password-string-expression--+----------------------------+--'
|                                                                 '-,--hint-string-expression--'
| 
|>--------------------------------------------------------------><
| 

|The schema is SYSIBM.

|This function was first available in FixPak 3 of Version 7.1.

|The ENCRYPT function returns a value that is the result of encrypting |data-string-expression. The password used for encryption is either |the password-string-expression value or the ENCRYPTION PASSWORD value |(as assigned using the SET ENCRYPTION PASSWORD statement).

|data-string-expression
|An expression that returns a CHAR or VARCHAR value to be encrypted. |The length attribute for the data type of data-string-expression is limited to 32663 without a hint-string-expression argument and 32631 when the hint-string-expression argument is specified (SQLSTATE 42815).

|password-string-expression
|An expression that returns a CHAR or VARCHAR value with at least 6 bytes |and no more than 127 bytes (SQLSTATE 428FC). The value represents the password |used to encrypt the data-string-expression. If the value |of the password argument is null or not provided, the data will be encrypted |using the ENCRYPTION PASSWORD value, which must have been set for the session |(SQLSTATE 51039).

|hint-string-expression
|An expression that returns a CHAR or VARCHAR value up to 32 bytes that |will help data owners remember passwords (for example, 'Ocean' as a hint to |remember 'Pacific'). If a hint value is given, the hint is embedded into the |result and can be retrieved using the GETHINT function. If this argument is |null or not provided, no hint will be embedded in the result. |

|The result data type of the function is VARCHAR FOR BIT DATA.

|The length attribute of the result is: |

|If the first argument can be null, the result can be null; if the first |argument is null, the result is the null value.

|Notice that the encrypted result is longer than the data-string-expression value. Therefore, when assigning encrypted values, ensure that |the target is declared with sufficient size to contain the entire encrypted |value.

|Notes: |

|Also see 38.3.2.2, DECRYPT_BIN and DECRYPT_CHAR and 38.3.2.4, GETHINT for additional |information on using this function.

|Examples:

|Example 1: This example uses the ENCRYPTION PASSWORD |value to hold the encryption password.

|   SET ENCRYPTION PASSWORD = 'Ben123';
|   INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832');

|Example 2: This example explicitly passes the encryption |password.

|   INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832','Ben123','');

|Example 3: The hint 'Ocean' is stored to help the |user remember the encryption password of 'Pacific'.

|   INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832','Pacific','Ocean');
| 

|38.3.2.4 GETHINT

| | | |
|>>-GETHINT--(--encrypted-data--)-------------------------------><
| 

|The schema is SYSIBM.

|This function was first available in FixPak 3 of Version 7.1.

|The GETHINT function will return the password hint if one is found in |the encrypted-data. A password hint is a phrase that |will help data owners remember passwords (For example, 'Ocean' as a hint to |remember 'Pacific').

|encrypted-data

|An expression that returns a CHAR FOR BIT DATA or VARCHAR FOR BIT |DATA value that is a complete, encrypted data string that was encrypted using |the ENCRYPT function (SQLSTATE 428FE). |

|The result of the function is VARCHAR(32). The result can be null; if the |hint parameter was not added to the encrypted-data by |the ENCRYPT function or the first argument is null, the result is the null |value.

|Also see 38.3.2.2, DECRYPT_BIN and DECRYPT_CHAR and 38.3.2.3, ENCRYPT for additional |information on using this function.

|Example:

|In this example the hint 'Ocean' is stored to help the user remember the |encryption password 'Pacific'.

|   INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832', 'Pacific','Ocean');
|   SELECT GETHINT(SSN)
|      FROM EMP;

|The value returned is 'Ocean'.

|38.3.2.5 IDENTITY_VAL_LOCAL

| | | |
|>>-IDENTITY_VAL_LOCAL--(--)------------------------------------><
| 

|The schema is SYSIBM.

|This procedure was first available in FixPak 3 of Version 7.1.

|The IDENTITY_VAL_LOCAL function is a non-deterministic function that |returns the most recently assigned value for an identity column, where the |assignment occurred as a result of a single row INSERT statement using a |VALUES clause. The function has no input parameters.

|The result is a DECIMAL(31,0), regardless of the actual data type of the |corresponding identity column.

|The value returned by the function is the value assigned to the identity |column of the table identified in the most recent single row INSERT statement. |The INSERT statement must be made using a VALUES clause on a table containing |an identity column. Also, the INSERT statement must be issued at the same |level |1 | (that is, the value is available locally at the |level it was assigned, until it is replaced by the next assigned value).

|The assigned value is either a value supplied by the user (if the identity |column is defined as GENERATED BY DEFAULT), or an identity value generated |by DB2.

|The function returns a null value in the following situations: |

|The result of the function is not affected by the following: |

|Notes: |

|Examples:

|Example 1: Set the variable IVAR to the value assigned to the identity |column in the EMPLOYEE table. If this insert is the first into the EMPLOYEE |table, then IVAR would have a value of 1.

|   CREATE TABLE EMPLOYEE
|      (EMPNO   INTEGER GENERATED ALWAYS AS IDENTITY,
|       NAME    CHAR(30),
|       SALARY  DECIMAL(5,2),
|       DEPTNO  SMALLINT)

|Example 2: An IDENTITY_VAL_LOCAL function invoked in an INSERT statement |returns the value associated with the previous single row INSERT statement, |with a VALUES clause for a table with an identity column. Assume for this |example that there are two tables, T1 and T2. Both T1 and T2 have an identity |column named C1. DB2 generates values in sequence, starting with 1, for |the C1 column in table T1, and values in sequence, starting with 10, for |the C1 column in table T2.

|   CREATE TABLE T1
|      (C1 INTEGER GENERATED ALWAYS AS IDENTITY,
|       C2 INTEGER),
|   CREATE TABLE T2
|      (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY
|         (START WITH 10),
|       C2 INTEGER),
|   INSERT INTO T1 (C2) VALUES (5),
|   INSERT INTO T1 (C2) VALUES (6),
|   SELECT * FROM T1

|which gives a result of:

|      C1            C2
|      -----------   ----------
|                1            5
|                2            6

|and now, declaring the function for |the variable IVAR:

|   VALUES IDENTITY_VAL_LOCAL() INTO :IVAR

|At this point, the IDENTITY_VAL_LOCAL function would return a value of |2 in IVAR, because that was the value most recently assigned by DB2. The |following INSERT statement inserts a single row into T2, where column C2 |gets a value of 2 from the IDENTITY_VAL_LOCAL function.

|INSERT INTO T2 (C2) VALUES (IDENTITY_VAL_LOCAL());
|   SELECT * FROM T2
|      WHERE C1 = DECIMAL(IDENTITY_VAL_LOCAL(),15,0)

|returning a result of:

|             C1                  C2
|      -----------------   ----------
|                    10.            2

|Invoking the IDENTITY_VAL_LOCAL |function after this insert results in a value of 10, which is the value |generated by DB2 for column C1 of T2.

|In a nested environment involving a trigger, use the IDENTITY_VAL_LOCAL |function to retrieve the identity value assigned at a particular level, |even though there might have been identity values assigned at lower levels. |Assume that there are three tables, EMPLOYEE, EMP_ACT, and ACCT_LOG. There |is an after insert trigger defined on EMPLOYEE that results in additional |inserts into the EMP_ACT and ACCT_LOG tables.

|   CREATE TABLE EMPLOYEE
|      (EMPNO SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 1000),
|       NAME CHAR(30),
|       SALARY DECIMAL(5,2),
|       DEPTNO SMALLINT);
| 
|   CREATE TABLE EMP_ACT
|      (ACNT_NUM SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 1),
|       EMPNO SMALLINT);
| 
|   CREATE TABLE ACCT_LOG
|      (ID SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 100),
|       ACNT_NUM SMALLINT,
|       EMPNO SMALLINT);
| 
|   CREATE TRIGGER NEW_HIRE
|      AFTER INSERT ON EMPLOYEE
|      REFERENCING NEW AS NEW_EMP
|      FOR EACH ROW MODE DB2SQL
|      BEGIN ATOMIC
|         INSERT INTO EMP_ACT (EMPNO)
|         VALUES (NEW_EMP.EMPNO);
|         INSERT INTO ACCT_LOG (ACNT_NUM EMPNO)     
|         VALUES (IDENTITY_VAL_LOCAL(), NEW_EMP.EMPNO);  
|   END

|The first triggered INSERT |statement inserts a row into the EMP_ACT table. This INSERT statement uses |a trigger transition variable for the EMPNO column of the EMPLOYEE table, |to indicate that the identity value for the EMPNO column of the EMPLOYEE |table is to be copied to the EMPNO column of the EMP_ACT table. The IDENTITY_VAL_LOCAL |function could not be used to obtain the value assigned to the EMPNO column |of the EMPLOYEE table. This is because an INSERT statement has not been |issued at this level of the nesting, and as such, if the IDENTITY_VAL_LOCAL |function were invoked in the VALUES clause of the INSERT for EMP_ACT, then |it would return a null value. This INSERT statement for the EMP_ACT table |also results in the generation of a new identity column value for the ACNT_NUM |column.

|A second triggered INSERT statement inserts a row into the ACCT_LOG table. |This statement invokes the IDENTITY_VAL_LOCAL function to indicate that |the identity value assigned to the ACNT_NUM column of the EMP_ACT table |in the previous INSERT statement in the triggered action is to be copied |to the ACNT_NUM column of the ACCT_LOG table. The EMPNO column is assigned |the same value as the EMPNO column of EMPLOYEE table.

|From the invoking application (that is, the level at which the INSERT to |EMPLOYEE is issued), set the variable IVAR to the value assigned to the |EMPNO column of the EMPLOYEE table by the original INSERT statement.

|   INSERT INTO EMPLOYEE (NAME, SALARY, DEPTNO)
|   VALUES ('Rupert', 989.99, 50);

|The contents |of the three tables after processing the original INSERT statement and all |of the triggered actions are:

|   SELECT EMPNO, SUBSTR(NAME,10) AS NAME, SALARY, DEPTNO
|      FROM EMPLOYEE;
| 
|      EMPNO       NAME        SALARY                             DEPTNO
|      ----------- ----------- ---------------------------------- -----------
|             1000 Rupert                                  989.99          50
| 
|   SELECT ACNT_NUM, EMPNO
|      FROM EMP_ACT;
| 
|      ACNT_NUM    EMPNO
|      ----------- -----------
|                1        1000
| 
|   SELECT * FROM ACCT_LOG;
| 
|      ID          ACNT_NUM    EMPNO
|      ----------- ----------- -----------
|              100           1        1000

|The result of the IDENTITY_VAL_LOCAL |function is the most recently assigned value for an identity column at the |same nesting level. After processing the original INSERT statement and all |of the triggered actions, the IDENTITY_VAL_LOCAL function returns a value |of 1000, because this is the value assigned to the EMPNO column of the EMPLOYEE |table. The following VALUES statement results in setting IVAR to 1000. The |insert into the EMP_ACT table (which occurred after the insert into the |EMPLOYEE table and at a lower nesting level) has no affect on what is returned |by this invocation of the IDENTITY_VAL_LOCAL function.

|VALUES IDENTITY_VAL_LOCAL() INTO :IVAR;

38.3.2.6 LCASE and UCASE (Unicode)

In a Unicode database, the entire repertoire of Unicode characters is uppercase (or lowercase) based on the Unicode properties of these characters. Double-wide versions of ASCII characters, as well as Roman numerals, now convert to upper or lower case correctly.

|38.3.2.7 MQPUBLISH

| | |
|>>-MQPUBLISH---(------------------------------------------------>
| 
|>-----+-----------------------------------------------+--------->
|      '-publisher-service--,--+--------------------+--'
|                              '-service-policy--,--'
| 
|>----msg-data----+-----------------------------------+--)------><
|                 '-,--topic--+--------------------+--'
|                             |              (1)   |
|                             '-,--correl-id-------'
| 

|Note:

  1. |The correl-id cannot be specified unless a service |and a policy are previously defined. |

|The schema is MQDB2.

|The MQPUBLISH function publishes data to MQSeries. This function requires |the installation of either MQSeries Publish/Subscribe or MQSeries Integrator. |Please consult www.ibm.com/software/MQSeries for further details.

|The MQPUBLISH function publishes the data contained in msg-data |to the MQSeries publisher specified in publisher-service, and using |the quality of service policy defined by service-policy. An optional |topic for the message can be specified, and an optional user-defined message |correlation identifier may also be specified. The function returns a value |of '1' if successful or a '0' if unsuccessful. |

|publisher-service
|A string containing the logical MQSeries destination where the message |is to be sent. If specified, the publisher-service must refer |to a publisher Service Point defined in the AMT.XML repository file. A |service point is a logical end-point from which a message is sent or received. |Service point definitions include the name of the MQSeries Queue Manager |and Queue. See the MQSeries Application Messaging Interface for further |details. If publisher-service is not specified, then the DB2.DEFAULT.PUBLISHER |will be used. The maximum size of publisher-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy to be used in handling |of this message. If specified, the service-policy must refer |to a Policy defined in the AMT.XML repository file. A Service Policy defines |a set of quality of service options that should be applied to this messaging |operation. These options include message priority and message persistence. |See the MQSeries Application Messaging Interface manual for further details. |If service-policy is not specified, then the default DB2.DEFAULT.POLICY |will be used. The maximum size of service-policy is 48 characters.

|msg-data
|A string expression containing the data to be sent via MQSeries. The |maximum size is 4000 characters.

|topic
|A string expression containing the topic for the message publication. |If no topic is specified, none will be associated with the message. The |maximum size of topic is 40 characters. Multiple topics can be |specified in one string (up to 40 characters long). Each topic must be separated |by a colon. For example, "t1:t2:the third topic" indicates that the message |is associated with all three topics: t1, t2, and "the third topic".

|correl-id
|An optional string expression containing a correlation identifier to |be associated with this message. The correl-id is often specified |in request and reply scenarios to associate requests with replies. If |not specified, no correlation id will be added to the message. The maximum |size of correl-id is 24 characters. |

|Examples

|Example 1: This example publishes the string "Testing 123" to the default |publisher service (DB2.DEFAULT.PUBLISHER) using the default policy (DB2.DEFAULT.POLICY). |No correlation identifier or topic is specified for the message.

|VALUES MQPUBLISH('Testing 123')

|Example 2: This example publishes the string "Testing 345" to the publisher |service "MYPUBLISHER" under the topic "TESTS". The default policy is used |and no correlation identifier is specified.

|VALUES MQPUBLISH('MYPUBLISHER','Testing 345', 'TESTS')

|Example 3: This example publishes the string "Testing 678" to the publisher |service "MYPUBLISHER" using the policy "MYPOLICY" with a correlation identifier |of "TEST1". The message is published with topic "TESTS".

|VALUES MQPUBLISH('MYPUBLISHER','MYPOLICY','Testing 678','TESTS','TEST1')

|Example 4: This example publishes the string "Testing 901" to the publisher |service "MYPUBLISHER" under the topic "TESTS" using the default policy |(DB2.DEFAULT.POLICY) and no correlation identifier.

|VALUES MQPUBLISH('Testing 901','TESTS')

|All examples return the value '1' if successful.

|38.3.2.8 MQREAD

| | |
|>>-MQREAD---(----+------------------------------------------+--->
|                 '-receive-service--+--------------------+--'
|                                    '-,--service-policy--'
| 
|>----)---------------------------------------------------------><
| 

|The schema is MQDB2.

|The MQREAD function returns a message from the MQSeries location specified |by receive-service, using the quality of service policy defined |in service-policy. Executing this operation does not remove the |message from the queue associated with receive-service, but instead |returns the message at the head of the queue. The return value is a VARCHAR(4000) |containing the message. If no messages are available to be returned, a NULL |is returned. |

|receive-service
|A string containing the logical MQSeries destination from where the |message is to be received. If specified, the receive-service |must refer to a Service Point defined in the AMT.XML repository file. |A service point is a logical end-point from where a message is sent or |received. Service points definitions include the name of the MQSeries |Queue Manager and Queue. See the MQSeries Application Messaging Interface |for further details. If receive-service is not specified, then |the DB2.DEFAULT.SERVICE will be used. The maximum size of receive-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy used in handling |this message. If specified, the service-policy must refer to |a Policy defined in the AMT.XML repository file. A Service Policy defines |a set of quality of service options that should be applied to this messaging |operation. These options include message priority and message persistence. |See the MQSeries Application Messaging Interface manual for further details. |If service-policy is not specified, then the default DB2.DEFAULT.POLICY |will be used. The maximum size of service-policy is 48 characters. |

|Examples:

|Example 1: This example reads the message at the head of the queue specified |by the default service (DB2.DEFAULT.SERVICE), using the default policy |(DB2.DEFAULT.POLICY).

|VALUES MQREAD()

|Example 2: This example reads the message at the head of the queue specified |by the service "MYSERVICE" using the default policy (DB2.DEFAULT.POLICY).

|VALUES MQREAD('MYSERVICE')

|Example 3: This example reads the message at the head of the queue specified |by the service "MYSERVICE", and using the policy "MYPOLICY".

|VALUES MQREAD('MYSERVICE','MYPOLICY')

|All of these examples return the contents of the message as a VARCHAR(4000) |if successful. If no messages are available, then a NULL is returned.

|38.3.2.9 MQRECEIVE

| | |
|>>-MQRECEIVE---------------------------------------------------->
| 
|>----(--+-------------------------------------------------------------+---)->
|        '-receive-service--+---------------------------------------+--'
|                           '-,--service-policy--+---------------+--'
|                                                '-,--correl-id--'
| 
|>--------------------------------------------------------------><
| 

|The schema is MQDB2.

|The MQRECEIVE function returns a message from the MQSeries location specified |by receive-service, using the quality of service policy service-policy. Performing this operation removes the message from the queue associated |with receive-service. If the correl-id is specified, then |the first message with a matching correlation identifier will be returned. |If correl-id is not specified, then the message at the head of |the queue will be returned. The return value is a VARCHAR(4000) containing |the message. If no messages are available to be returned, a NULL is returned. |

|receive-service
|A string containing the logical MQSeries destination from which |the message is received. If specified, the receive-service must |refer to a Service Point defined in the AMT.XML repository file. A service |point is a logical end-point from which a message is sent or received. |Service points definitions include the name of the MQSeries Queue Manager |and Queue. See the MQSeries Application Messaging Interface for further |details. If receive-service is not specified, then the DB2.DEFAULT.SERVICE |is used. The maximum size of receive-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy to be used in |the handling of this message. If specified, the service-policy |must refer to a Policy defined in the AMT XML repository file |4 |. If service-policy is |not specified, then the default DB2.DEFAULT.POLICY is used. The maximum |size of service-policy is 48 characters.

|correl-id
|A string containing an optional correlation identifier to be associated |with this message. The correl-id is often specified in request |and reply scenarios to associate requests with replies. If not specified, |no correlation id will be specified. The maximum size of correl-id is 24 characters. |

|Examples:

|Example 1: This example receives the message at the head of the queue |specified by the default service (DB2.DEFAULT.SERVICE), using the default |policy (DB2.DEFAULT.POLICY).

|VALUES MQRECEIVE()

|Example 2: This example receives the message at the head of the queue |specified by the service "MYSERVICE" using the default policy (DB2.DEFAULT.POLICY).

|VALUES MQRECEIVE('MYSERVICE')

|Example 3: This example receives the message at the head of the queue |specified by the service "MYSERVICE" using the policy "MYPOLICY".

|VALUES MQRECEIVE('MYSERVICE','MYPOLICY')

|Example 4: This example receives the first message with a correlation id |that matches '1234' from the head of the queue specified by the service |"MYSERVICE" using the policy "MYPOLICY".

|VALUES MQRECEIVE('MYSERVICE',MYPOLICY','1234')

|All these examples return the |contents of the message as a VARCHAR(4000) if successful. If no messages |are available, a NULL will be returned.

|38.3.2.10 MQSEND

| | |

|>>-MQSEND---(----+------------------------------------------+--->
|                 '-send-service--,--+--------------------+--'
|                                    '-service-policy--,--'
| 
|>----msg-data----+--------------------+--)---------------------><
|                 |              (1)   |
|                 '-,--correl-id-------'
| 

|Note:

  1. |The correl-id cannot be specified unless a service |and a policy are previously defined. |

|The schema is MQDB2.

|The MQSEND function sends the data contained in msg-data to the |MQSeries location specified by send-service, using the quality |of service policy defined by service-policy. An optional user defined |message correlation identifier may be specified by correl-id. The |function returns a value of '1' if successful or a '0' if unsuccessful. |

|msg-data
|A string expression containing the data to be sent via MQSeries. The |maximum size is 4000 characters.

|send-service
|A string containing the logical MQSeries destination where the message |is to be sent. If specified, the send-service refers to a service |point defined in the AMT.XML repository file. A service point is a logical |end-point from which a message may be sent or received. Service point |definitions include the name of the MQSeries Queue Manager and Queue. |See the MQSeries Application Messaging Interface manual for further details. |If send-service is not specified, then the value of DB2.DEFAULT.SERVICE |is used. The maximum size of send-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy used in handling |of this message. If specified, the service-policy must refer |to a service policy defined in the AMT XML repository file. A Service |Policy defines a set of quality of service options that should be applied |to this messaging operation. These options include message priority and |message persistence. See the MQSeries Application Messaging Interface |manual for further details. If service-policy is not specified, |then a default value of DB2.DEFAULT.POLICY will be used. The maximum size |of service-policy is 48 characters.

|correl-id
|An optional string containing a correlation identifier associated with |this message. The correl-id is often specified in request and reply |scenarios to associate requests with replies. If not specified, no correlation |id will be sent. The maximum size of correl-id is 24 characters. |

|Examples:

|Example 1: This example sends the string "Testing 123" to the default service |(DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY), with |no correlation identifier.

|VALUES MQSEND('Testing 123')

|Example 2: This example sends the string "Testing 345" to the service |"MYSERVICE", using the policy "MYPOLICY", with no correlation identifier.

|VALUES MQSEND('MYSERVICE','MYPOLICY','Testing 345')

|Example 3: This example sends the string "Testing 678" to the service |"MYSERVICE", using the policy "MYPOLICY", with correllation identifier "TEST3".

|VALUES MQSEND('MYSERVICE','MYPOLICY','Testing 678','TEST3')

|Example 4: This example sends the string "Testing 901" to the service |"MYSERVICE", using the default policy (DB2.DEFAULT.POLICY), and no correlation |identifier.

|VALUES MQSEND('MYSERVICE','Testing 901')

|All examples return a scalar value of '1' if successful.

|38.3.2.11 MQSUBSCRIBE

| | |
|>>-MQSUBSCRIBE---(---------------------------------------------->
| 
|>-----+------------------------------------------------+-------->
|      '-subscriber-service--,--+--------------------+--'
|                               '-service-policy--,--'
| 
|>----topic---)-------------------------------------------------><
| 

|The schema is MQDB2.

|The MQSUBSCRIBE function is used to register interest in MQSeries messages |published on a specified topic. The subscriber-service specifies |a logical destination for messages that match the specified topic. Messages |that match topic will be placed on the queue defined by subscriber-service and can be read or received through a subsequent call to MQREAD, MQRECEIVE, |MQREADALL, or MQRECEIVEALL. This function requires the installation and |configuration of an MQSeries based publish and subscribe system, such as |MQSeries Integrator or MQSeries Publish/Subscribe. See www.ibm.com/software/MQSeries |for further details.

|The function returns a value of '1' if successful or a '0' if unsuccessful. |Successfully executing this function will cause the publish and subscribe |server to forward messages matching the topic to the service point defined |by subscriber-service. |

|subscriber-service
|A string containing the logical MQSeries subscription point to where |messages matching topic will be sent. If specified, the subscriber-service must refer to a Subscribers Service Point defined |in the AMT.XML repository file. Service points definitions include the |name of the MQSeries Queue Manager and Queue. See the MQSeries Application |Messaging Interface manual for further details. If subscriber-service is not specified, then the DB2.DEFAULT.SUBSCRIBER will be used instead. |The maximum size of subscriber-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy to be used in handling |the message. If specified, the service-policy must refer to a |Policy defined in the AMT.XML repository file. A Service Policy defines |a set of quality of service options to be applied to this messaging operation. |These options include message priority and message persistence. See the |MQSeries Application Messaging Interface manual for further details. If service-policy is not specified, then the default DB2.DEFAULT.POLICY |will be used instead. The maximum size of service-policy is 48 characters.

|topic
|A string defining the types of messages to receive. Only messages published |with the specified topics will be received by this subscription. Multiple |subscriptions may coexist. The maximum size of topic is 40 characters. Multiple |topics can be specified in one string (up to 40 characters long). Each topic |must be separated by a colon. For example, "t1:t2:the third topic" indicates |that the message is associated with all three topics: t1, t2, and "the third |topic". |

|Examples:

|Example 1: This example registers an interest in messages containing the |topic "Weather". The default subscriber-service (DB2.DEFAULT.SUBSCRIBER) |is registered as the subscriber and the default service-policy (DB2.DEFAULT.POLICY) |specifies the quality of service.

|VALUES MQSUBSCRIBE('Weather')

|Example 2: This example demonstrates a subscriber registering interest |in messages containing "Stocks". The subscriber registers as "PORTFOLIO-UPDATES" |with policy "BASIC-POLICY".

|VALUES MQSUBSCRIBE('PORTFOLIO-UPDATES','BASIC-POLICY','Stocks')

|All examples return a scalar value of '1' if successful.

|38.3.2.12 MQUNSUBSCRIBE

| | |
|>>-MQUNSUBSCRIBE---(-------------------------------------------->
| 
|>-----+------------------------------------------------+-------->
|      '-subscriber-service--,--+--------------------+--'
|                               '-service-policy--,--'
| 
|>----topic---)-------------------------------------------------><
| 

|The schema is MQDB2.

|The MQUNSUBSCRIBE function is used to unregister an existing message subscription. |The subscriber-service, service-policy, and topic are used to identify which subscription is cancelled. This function |requires the installation and configuration of an MQSeries based publish |and subscribe system, such as MQSeries Integrator or MQSeries Publish/Subscribe. |See www.ibm.com/software/MQSeries for further details.

|The function returns a value of '1' if successful or a '0' if unsuccessful. |The result of successfully executing this function is that the publish and |subscribe server will remove the subscription defined by the given parameters. |Messages with the specified topic will no longer be sent to the |logical destination defined by subscriber-service. |

|subscriber-service
|If specified, the subscriber-service must refer to a Subscribers |Service Point defined in the AMT.XML repository file. Service point definitions |include the name of the MQSeries Queue Manager and Queue. See the MQSeries |Application Messaging Interface manual for further details. If subscriber-service is not specified, then the DB2.DEFAULT.SUBSCRIBER value is used. |The maximum size of subscriber-service is 48 characters.

|service-policy
|If specified, the service-policy must refer to a Policy defined |in the AMT.XML repository file. A Service Policy defines a set of quality |of service options to be applied to this messaging operation. See the |MQSeries Application Messaging Interface manual for further details. If service-policy is not specified, then the default DB2.DEFAULT.POLICY |will be used. The maximum size of service-policy is 48 characters.

|topic
|A string specifying the subject of messages that are not to be received. |The maximum size of topic is 40 characters. Multiple topics can |be specified in one string (up to 40 characters long). Each topic must be |separated by a colon. For example, "t1:t2:the third topic" indicates that |the message is associated with all three topics: t1, t2, and "the third topic". |

|Examples:

|Example 1: This example cancels an interest in messages containing the |topic "Weather". The default subscriber-service (DB2.DEFAULT.SUBSCRIBER) |is registered as the unsubscriber and the default service-policy (DB2.DEFAULT.POLICY) |specifies the quality of service.

|VALUES MQUNSUBSCRIBE('Weather')

|Example 2: This example demonstrates a subscriber cancelling an interest |in messages containing "Stocks". The subscriber is registered as "PORTFOLIO-UPDATES" |with policy "BASIC-POLICY".

|VALUES MQUNSUBSCRIBE('PORTFOLIO-UPDATES','BASIC-POLICY','Stocks')

|These examples return a scalar value of '1' if successful and a scalar |value of '0' if unsuccessful.

|38.3.2.13 MULTIPLY_ALT

|>>-MULTIPLY_ALT------------------------------------------------->
| 
|>----(exact_numeric_expression, exact_numeric_expression)------><
| 

|The schema is SYSIBM.

|This function was first available in FixPak 2 of Version 7.1.

|The MULTIPLY_ALT scalar function returns the product of the two arguments |as a decimal value. It is provided as an alternative to the multiplication |operator, especially when the sum of the precisions of the arguments exceeds |31.

|The arguments can be any built-in exact numeric data type (DECIMAL, BIGINT, |INTEGER, or SMALLINT).

|The result of the function is a DECIMAL. The precision and scale of the |result are determined as follows, using the symbols p and s to denote the |precision and scale of the first argument, and the symbols p' and s' to denote |the precision and scale of the second argument. |

|The result can be null if at least one argument can be null or the database |is configured with DFT_SQLMATHWARN set to yes; the result is the null value |if one of the arguments is null.

|The MULTIPLY_ALT function is a better choice than the multiplication operator |when performing decimal arithmetic where a scale of at least 3 is needed and |the sum of the precisions exceeds 31. In these cases, the internal computation |is performed so that overflows are avoided. The final result is then assigned |to the result data type using truncation where necessary to match the scale. |Note that overflow of the final result is still possible when the scale is |3.

|The following is a sample comparing the result types using MULTIPLY_ALT |and the multiplication operator.


|
Type of argument 1 Type of argument 2 Result using MULTIPLY_ALT Result using multiplication operator
DECIMAL(31,3) DECIMAL(15,8) DECIMAL(31,3) DECIMAL(31,11)
DECIMAL(26,23) DECIMAL(10,1) DECIMAL(31,19) DECIMAL(31,24)
DECIMAL(18,17) DECIMAL(20,19) DECIMAL(31,29) DECIMAL(31,31)
DECIMAL(16,3) DECIMAL(17,8) DECIMAL(31,9) DECIMAL(31,11)
DECIMAL(26,5) DECIMAL(11,0) DECIMAL(31,3) DECIMAL(31,5)
DECIMAL(21,1) DECIMAL(15,1) DECIMAL(31,2) DECIMAL(31,2)

|Example:

|Multiply two values where the data type of the first argument is DECIMAL(26, |3) and the data type of the second argument is DECIMAL(9,8). The data type |of the result is DECIMAL(31,7).

|values multiply_alt(98765432109876543210987.654,5.43210987)
|1
|---------------------------------
| 536504678578875294857887.5277415  

|Note that the complete product of |these two numbers is 536504678578875294857887.52774154498 but the |last 4 digits were truncated to match the scale of the result data type. Using |the multiplication operator with the same values results in an arithmetic |overflow since the result data type is DECIMAL(31,11) and the result value |has 24 digits left of the decimal, but the result data type only supports |20 digits.

|38.3.2.14 REC2XML

| | |
|>>-REC2XML---(--decimal-constant---,--format-string------------->
| 
|>----,--row-tag-string----+------------------------+--)--------><
|                          |  .------------------.  |
|                          |  V                  |  |
|                          '----,--column-name---+--'
| 

|The schema is SYSIBM.

|The REC2XML function returns a string formatted with XML tags and containing |column names and column values. |

|decimal-constant
|The expansion factor for replacing column value characters. The decimal |value must be greater than 0.0 and less than or equal to 6.0 (SQLSTATE 42820).

|The decimal-constant value is used to calculate the result length |of the function. For every column with a character data type, the length attribute of the column is multiplied |by this expansion factor before it is added in to the result length.

|To specify no expansion, use a value of 1.0. Specifying a value |less than 1.0 reduces the calculated result length. If the actual length |of the result string is greater than the calculated result length of |the function, then an error is raised (SQLSTATE 22001).

|format-string
|The string constant that specifies which format the function is to use |during execution.

|The format-string is case-sensitive, so the |following values must be specified in uppercase to be recognized.

|

|COLATTVAL or COLATTVAL_XML
|These formats return a string with columns as attribute values.

|>>-<--row-tag-string-->----------------------------------------->
| 
|      .-------------------------------------------------------------------------.
|      V                                                                         |
|>--------<--column-name--=--"column-name"--+->--column-value--</--column-->--+--+>
|                                           '-null="true"--</-----------------'
| 
|>----</--row-tag-string-->-------------------------------------><
| 

|Column names may or may not be valid XML attribute values. For |those column names which are not valid XML attribute values, character replacement |is performed on the column name before it is included in the result string.

|Column values may or may not be valid XML element values. If the format-string COLATTVAL is specified, for those column values which are |not valid XML element values, character replacement is performed on the column |value before it is included in the result string. If the format-string COLATTVAL_XML is specified, character replacement is not performed on |column values (note that character replacement is still performed on column |names). |

|row-tag-string
|A string constant that specifies the tag used for each row.

|If an |empty string is specified, then a value of 'row' is assumed.

|If a string |of one or more blank characters is specified, then no beginning row-tag-string or ending row-tag-string (including the angle bracket delimiters) |will appear in the result string.

|column-name
|A qualified or unqualified name of a table column. The column must |have one of the following data types (SQLSTATE 42815): |

|The same column name cannot be specified more than once (SQLSTATE 42734). |

|The result of the function is VARCHAR. The maximum length is 32672 bytes |(SQLSTATE 54006).

|Consider the following invocation:

|   REC2XML (dc, fs, rt, c1, c2, ..., cn)
|

|If the value of fs is "COLATTVAL" or "COLATTVAL_XML"
|the result is the same as the following expression:

|   '<' CONCAT rt CONCAT '>' CONCAT y1 CONCAT y2 CONCAT ... CONCAT yn 
|   CONCAT '</' CONCAT rt CONCAT '>'

|where |yn is equivalent to:

|   '<column name="' CONCAT xvcn CONCAT vn

|and vn is equivalent to:

|   '">' CONCAT rn CONCAT '</column>'

|if |the column is not null, and

|   '" null="true"/>'

|if the column value is null.

|xvcn |is equivalent to a string representation of the column name of cn, where any characters appearing in Table 29 are replaced |with the corresponding representation. This ensures that the resulting string |is a valid XML attribute or element value token.

|rn is equivalent to a string representation as indicated in Table 28. |

|Result Column Values:

|Based on the data type of the column and the actual format-string |specified, the column values from the table may be transformed before being |concatenated into the result string. The following table shows the transformations |done on the column values.
|

|Table 28. Column Values String Result
Data type of cn rn
CHAR, VARCHAR The value is a string. If the format-string does not end in the characters "_XML", then each character in cn is replaced with the corresponding replacement representation from Table 29, as indicated. The length attribute is: dc * the length attribute of cn.
SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE The value is LTRIM(RTRIM(CHAR(cn))). The length attribute is the result length of CHAR(cn). The decimal character is always the period character.
DATE The value is CHAR(cn, ISO). The length attribute is the result length of CHAR(cn, ISO).
TIME The value is CHAR(cn, JIS). The length attribute is the result length of CHAR(cn, JIS)
TIMESTAMP The value is CHAR(cn). The length attribute is the result length of CHAR(cn).

|Character Replacement:

|Depending on the value specified for the format-string, certain |characters in column names and column values will be replaced to ensure |that the column names form valid XML attribute values and the column values |form valid XML element values.


|

|Table 29. Character Replcements for XML Attribute Values and Element Values
< is replaced by &lt;
> is replaced by &gt;
" is replaced by &quot;
& is replaced by &amp;
' is replaced by &apos;

|Examples: |

|38.3.2.15 ROUND

|>>-ROUND---(expression1, expression2)--------------------------><
| 

|The schema is SYSIBM.

|This function was first available in FixPak 2 of Version 7.1.

|Note:
The SYSFUN version of the ROUND function continues to be available. |

|The ROUND function returns expression1 rounded |to expression2 places to the right of the decimal |point if expression2 is positive or to the left |of the decimal point if expression2 is zero or |negative.

|If expression1 is positive, a value of 5 is |rounded to the next higher positive number. For example, ROUND(3.5,0) = 4. |If expression1 is negative, a value of 5 is rounded |to the next lower negative number. For example, ROUND(-3.5,0) = -4. |

|expression1
|An expression that returns a value of any built-in numeric data type.

|expression2
|An expression that returns a small or large integer. When the value |of expression2 is not negative, it specifies |rounding to that number of places to the right of the decimal separator. When |the value of expression2 is negative, it specifies |rounding to the absolute value of expression2 |places to the left of the decimal separator.

|If expression2 is not negative, expression1 is rounded to the absolute value |of expression2 number of places to the right |of the decimal point. If the value of expression2 is greater than the scale of expression1 |then the value is unchanged except that the result value has a precision that |is larger by 1. For example, ROUND(748.58,5) = 748.58 where the precision |is now 6 and the scale remains 2.

|If expression2 is negative, expression1 is rounded to |the absolute value of expression2+1 number of |places to the left of the decimal point.

|If the absolute value of a |negative expression2 is larger than the number |of digits to the left of the decimal point, the result is 0. For example, |ROUND(748.58,-4) = 0. |

|The data type and length attribute of the result are the same as the data |type and length attribute of the first argument, except that the precision |is increased by one if the expression1 is DECIMAL |or NUMERIC and the precision is less than 31. For example, an argument with |a data type of DECIMAL(5,2) results in DECIMAL(6,2). An argument with a data |type of DECIMAL(31,2) results in DECIMAL(31,2). The scale is the same as the |scale of the first argument.

|If either argument can be null or the database is configured with DFT_SQLMATHWARN |set to yes, the result can be null. If either argument is null, the result |is the null value.

|Examples:

|Calculate the number 873.726 rounded to 2, 1, 0, -1, -2, -3, and -4 decimal |places respectively.

|    VALUES (ROUND(873.726, 2),
|            ROUND(873.726, 1),
|            ROUND(873.726, 0),
|            ROUND(873.726,-1),
|            ROUND(873.726,-2),
|            ROUND(873.726,-3),
|            ROUND(873.726,-4) )

|This example returns:

|1         2         3         4         5         6         7
|--------- --------- --------- --------- --------- --------- ---------
|  873.730   873.700   874.000   870.000   900.000  1000.000  0.000

|Calculate |a both positive and negative numbers.

|    VALUES (ROUND(3.5, 0),
|            ROUND(3.1, 0),
|            ROUND(-3.1, 0),
|            ROUND(-3.5,0) )

|This example returns:

|     1    2    3    4
|     ---- ---- ---- ----
|      4.0  3.0 -3.0 -4.0

38.3.2.16 WEEK_ISO

Change the description of this function to the following:

The schema is SYSFUN.

Returns the week of the year of the argument as an integer value in the range 1-53. The week starts with Monday and always includes 7 days. Week 1 is the first week of the year to contain a Thursday, which is equivalent to the first week containing January 4. It is therefore possible to have up to 3 days at the beginning of a year appear in the last week of the previous year. Conversely, up to 3 days at the end of a year may appear in the first week of the next year.

The argument must be a date, timestamp, or a valid character string representation of a date or timestamp that is neither a CLOB nor a LONG VARCHAR.

The result of the function is INTEGER. The result can be null; if the argument is null, the result is the null value.

Example:

The following list shows examples of the result of WEEK_ISO and DAYOFWEEK_ISO.

DATE       WEEK_ISO    DAYOFWEEK_ISO    
---------- ----------- -------------    
1997-12-28          52             7    
1997-12-31           1             3    
1998-01-01           1             4    
1999-01-01          53             5    
1999-01-04           1             1    
1999-12-31          52             5    
2000-01-01          52             6    
2000-01-03           1             1

38.3.3 Table Functions

|38.3.3.1 MQREADALL

| | |
|>>-MQREADALL---(------------------------------------------------>
| 
|>-----+------------------------------------------+-------------->
|      '-receive-service--+--------------------+--'
|                         '-,--service-policy--'
| 
|>-----+-----------+--)-----------------------------------------><
|      '-num-rows--'
| 

|The schema is MQDB2.

|The MQREADALL function returns a table containing the messages and message |metadata from the MQSeries location specified by receive-service, |using the quality of service policy service-policy. Performing |this operation does not remove the messages from the queue associated with receive-service.

|If num-rows is specified, then a maximum of num-rows |messages will be returned. If num-rows is not specified, then all |available messages will be returned. The table returned contains the following |columns: |

|

|receive-service
|A string containing the logical MQSeries destination from which the |message is read. If specified, the receive-service must refer |to a service point defined in the AMT.XML repository file. A service point |is a logical end-point from which a message is sent or received. Service |point definitions include the name of the MQSeries Queue Manager and Queue. |See the MQSeries Application Messaging Interface for further details. |If receive-service is not specified, then the DB2.DEFAULT.SERVICE |will be used. The maximum size of receive-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy used in the |handling of this message. If specified, the service-policy refers |to a Policy defined in the AMT XML repository file. A service policy |defines a set of quality of service options that should be applied to this |messaging operation. These options include message priority and message |persistence. See the MQSeries Application Messaging Interface manual for |further details. If service-policy is not specified, then |the default DB2.DEFAULT.POLICY will be used. The maximum size of service-policy is 48 characters.

|num-rows
|A positive integer containing the maximum number of messages to be returned |by the function. |

|Examples:

|Example 1: This example receives all the messages from the queue specified |by the default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY). |The messages and all the metadata are returned as a table.

|SELECT *
|   FROM table (MQREADALL()) T

|Example 2: This example receives all the messages from the head of the |queue specified by the service MYSERVICE, using the default policy (DB2.DEFAULT.POLICY). |Only the MSG and CORRELID columns are returned.

|SELECT T.MSG, T.CORRELID
|   FROM table (MQREADALL('MYSERVICE')) T

|Example 3: This example reads the head of the queue specified by the |default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY). |Only messages with a CORRELID of '1234' are returned. All columns are returned.

|SELECT *
|   FROM table (MQREADALL()) T
|   WHERE T.CORRELID = '1234'

|Example 4: This example receives the first 10 messages from the head |of the queue specified by the default service (DB2.DEFAULT.SERVICE), using |the default policy (DB2.DEFAULT.POLICY). All columns are returned.

|SELECT *
|   FROM table (MQREADALL(10)) T

|38.3.3.2 MQRECEIVEALL

| | |
|>>-MQRECEIVEALL---(--------------------------------------------->
| 
|>-----+-------------------------------------------------------------+>
|      '-receive-service--+---------------------------------------+--'
|                         '-,--service-policy--+---------------+--'
|                                              '-,--correl-id--'
| 
|>-----+--------------------+--)--------------------------------><
|      '-+----+---num-rows--'
|        '-,--'
| 

|The schema is MQDB2.

|The MQRECEIVEALL function returns a table containing the messages and message |metadata from the MQSeries location specified by receive-service, |using the quality of service policy service-policy. Performing |this operation removes the messages from the queue associated with receive-service.

|If a correl-id is specified, then only those messages with a |matching correlation identifier will be returned. If correl-id |is not specified, then the message at the head of the queue will be returned.

|If num-rows is specified, then a maximum of num-rows |messages will be returned. If num-rows is not specified, then all |available messages are returned. The table returned contains the following |columns: |

|

|receive-service
|A string containing the logical MQSeries destination from which the |message is recieved. If specified, the receive-service must refer |to a service point defined in the AMT.XML repository file. A service point |is a logical end-point from which a message is sent or received. Service |point definitions include the name of the MQSeries Queue Manager and Queue. |See the MQSeries Application Messaging Interface manual for further details. |If receive-service is not specified, then the DB2.DEFAULT.SERVICE |will be used. The maximum size of receive-service is 48 characters.

|service-policy
|A string containing the MQSeries AMI Service Policy used in the |handling of this message. If specified, the service-policy refers |to a Policy defined in the AMT XML repository file. A service policy |defines a set of quality of service options that should be applied to this |messaging operation. These options include message priority and message |persistence. See the MQSeries Application Messaging Interface manual for |further details. If service-policy is not specified, then |the default DB2.DEFAULT.POLICY will be used. The maximum size of service-policy is 48 characters.

|correl-id
|An optional string containing a correlation identifier associated |with this message. The correl-id is often specified in request |and reply scenarios to associate requests with replies. If not specified, |no correlation id is specified. The maximum size of correl-id is 24 characters.

|num-rows
|A positive integer containing the maximum number of messages to be returned |by the function. |

|Examples:

|Example 1: This example receives all the messages from the queue specified |by the default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY). |The messages and all the metadata are returned as a table.

|SELECT *
|   FROM table (MQRECEIVEALL()) T

|Example 2: This example receives all the messages from the head of the |queue specified by the service MYSERVICE, using the default policy (DB2.DEFAULT.POLICY). |Only the MSG and CORRELID columns are returned.

|SELECT T.MSG, T.CORRELID
|   FROM table (MQRECEIVEALL('MYSERVICE')) T

|Example 3: This example receives all of the message from the head of the |queue specified by the service "MYSERVICE", using the policy "MYPOLICY". |Only messages with a CORRELID of '1234' are returned. Only the MSG and CORRELID |columns are returned.

|SELECT T.MSG, T.CORRELID
|   FROM table (MQRECEIVEALL('MYSERVICE','MYPOLICY','1234')) T
|  

|Example 4: This example receives the first 10 messages from the head |of the queue specified by the default service (DB2.DEFAULT.SERVICE), using |the default policy (DB2.DEFAULT.POLICY). All columns are returned.

|SELECT *
|   FROM table (MQRECEIVEALL(10)) T

|38.3.4 Procedures

| |

|38.3.4.1 GET_ROUTINE_SAR

|>>-GET_ROUTINE_SAR---------------------------------------------->
| 
|>----(--sarblob--,--type--,--routine_name_string--)------------><
| 

|The schema is SYSFUN.

|This procedure was first available in FixPak 3 of Version 7.1.

|The GET_ROUTINE_SAR procedure retrieves the necessary information to |install the same routine in another database server running the same level |on the same operating system. The information is retrieved into a single |BLOB string representing an SQL archive file. The invoker of the GET_ROUTINE_SAR |procedure must have DBADM authority. |

|sarblob
|An output argument of type BLOB(3M) that contains the routine SAR |file contents.

|type
|An input argument of type CHAR(2) that specifies whether the type |of routine, using one of the following values: |

|routine_name_string
|An input argument of type VARCHAR(257) that specifies a qualified |name of the routine. If no schema name is specified, the default is the |CURRENT SCHEMA when the routine is processed. |
|Note:
The routine_name_string cannot include the double |quote character ("). |

|The qualified name of the routine is used to determine which routine |to retrieve. The routine that is found must be an SQL routine or an error |is raised (SQLSTATE 428F7). When not using a specific name, this may result |in more than one routine and an error is raised (SQLSTATE 42725). If this |occurs, the specific name of the routine must be used to get the routine.

|The SAR file must include a bind file which may not be available at the |server. If the bind file cannot be found and stored in the SAR file, an |error is raised (SQLSTATE 55045).

|38.3.4.2 PUT_ROUTINE_SAR

|>>-PUT_ROUTINE_SAR---------------------------------------------->
| 
|>----(--sarblob--+-------------------------------------+--)----><
|                 '-,--new_owner--,--use_register_flag--'
| 

|The schema is SYSFUN.

|This procedure was first available in FixPak 3 of Version 7.1.

|The PUT_ROUTINE_SAR procedure passes the necessary file to create an SQL |routine at the server and then defines the routine. The invoker of the PUT_ROUTINE_SAR |procedure must have DBADM authority. |

|sarblob
|An input argument of type BLOB(3M) that contains the routine SAR |file contents.

|new_owner
|An input argument of type VARCHAR(128) that contains an authorization-name |used for authorization checking of the routine. The new-owner |must have the necessary privileges for the routine to be defined. If new-owner is not specified, the authorization-name of the original |routine definer is used.

|use_register_flag
|An input argument of type INTEGER that indicates whether or not |the CURRENT SCHEMA and CURRENT PATH special registers are used to define |the routine. If the special registers are not used, the settings for the |default schema and SQL path are the settings used when the routine was |originally defined. Possible values for use-register-flag:

|

|0
|Do not use the special registers of the current environment

|1
|Use the CURRENT SCHEMA and CURRENT PATH special registers. |

|If the value is 1, CURRENT SCHEMA is used for unqualified object |names in the routine definition (including the name of the routine) |and CURRENT PATH is used to resolve unqualified routines and data types |in the routine definition. If the use-registers-flag is not specified, |the behavior is the same as if a value of 0 was specified. |

|The identification information contained in sarblob is checked |to confirm that the inputs are appropriate for the environment, otherwise |an error is raised (SQLSTATE 55046). The PUT_ROUTINE_SAR procedure then uses |the contents of the sarblob to define the routine at the server.

|The contents of the sarblob argument are extracted into the separate |files that make up the SQL archive file. The shared library and bind files |are written to files in a temporary directory. The environment is set so |that the routine definition statement processing is aware that compiling |and linking are not required, and that the location of the shared library |and bind files is available. The contents of the DDL file are then used to |dynamically execute the routine definition statement.

|Note:
No more than one procedure can be concurrently installed |under a given schema. |

|Processing of this statement may result in the same errors as executing |the routine definition statement using other interfaces. During routine definition |processing, the presence of the shared library and bind files is noted and |the precompile, compile and link steps are skipped. The bind file is used |during bind processing and the contents of both files are copied to the usual |directory for an SQL routine.

|Note:
If a GET ROUTINE or a PUT ROUTINE operation (or their corresponding |procedure) fails to execute successfully, it will always return an error |(SQLSTATE 38000), along with diagnostic text providing information about |the cause of the failure. For example, if the procedure name provided to |GET ROUTINE does not identify an SQL procedure, diagnostic "100, 02000" |text will be returned, where "100" and "02000" are the SQLCODE and SQLSTATE, |respectively, that identify the cause of the problem. The SQLCODE and SQLSTATE |in this example indicate that the row specified for the given procedure |name was not found in the catalog tables. |

Footnotes:

1
A new level is initiated each time a trigger, function, or stored procedure is invoked.

2
Unless the automatic commit is turned off, interfaces that automatically commit after each statement will return a null value when the function is invoked in separate statements.

3
This applies to both FOR EACH ROW and FOR EACH STATEMENT after insert triggers.

4
A Service Policy defines a set of quality of service options that should be applied to this messaging operation. These options include message priority and message persistence. See the MQSeries Application Messaging Interface manual for further details.

5
A character string with a subtype of BIT DATA is not allowed.


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]