|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.
|>>-+-ABS----+--(expression)------------------------------------>< | '-ABSVAL-' |
|The schema is SYSIBM.
|This function was first available in FixPak 2 of Version 7.1.
|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.
|>>-+-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).
|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'.
|>>-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).
|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: |
|Table Column Definition: When defining |columns and types to contain encrypted data always calculate the length attribute |as follows. For encrypted data with no hint:
|Maximum length of the |non-encrypted data + 8 bytes + the number of bytes to the next 8 byte boundary |= encrypted data column length.
|For encrypted data with embedded hint |:
|Maximum length of the non-encrypted data + 8 bytes + the number |of bytes to the next 8 byte boundary + 32 bytes for the hint length = encrypted |data column length.
|Any assignment or cast to a length shorter than |the suggested data length may result in failed decryption in the future and lost data. Blanks are valid encrypted data values that may |be truncated when stored in a column that is too short.
|Sample Column |Length Calculations
|Maximum length of non-encrypted data 6 bytes |8 bytes 8 bytes |Number of bytes to the next 8 byte boundary 2 bytes | --------- |Encrypted data column length 16 bytes | |Maximum length of non-encrypted data 32 bytes |8 bytes 8 bytes |Number of bytes to the next 8 byte boundary 8 bytes | --------- |Encrypted data column length 48 bytes
|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'); |
|>>-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').
|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'.
|>>-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;
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.
|>>-MQPUBLISH---(------------------------------------------------> | |>-----+-----------------------------------------------+---------> | '-publisher-service--,--+--------------------+--' | '-service-policy--,--' | |>----msg-data----+-----------------------------------+--)------>< | '-,--topic--+--------------------+--' | | (1) | | '-,--correl-id-------' |
|Note:
|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. |
|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.
|>>-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. |
|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.
|>>-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. |
|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.
|>>-MQSEND---(----+------------------------------------------+---> | '-send-service--,--+--------------------+--' | '-service-policy--,--' | |>----msg-data----+--------------------+--)--------------------->< | | (1) | | '-,--correl-id-------' |
|Note:
|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. |
|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.
|>>-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. |
|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.
|>>-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. |
|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.
|>>-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.
|>>-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. |
|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).
|The format-string is case-sensitive, so the |following values must be specified in uppercase to be recognized.
|
|>>-<--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). |
|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.
|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)
|
| '<' 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 < |
> is replaced by > |
" is replaced by " |
& is replaced by & |
' is replaced by ' |
|Examples: |
| SELECT REC2XML (1.0, 'COLATTVAL', '', DEPTNO, MGRNO, ADMRDEPT) | FROM DEPARTMENT | WHERE DEPTNO = 'D01'
|This example |returns the following VARCHAR(117) string:
|<row> | <column name="DEPTNO">D01</column> | <column name="MGRNO" null="true"/> | <column name="ADMRDEPT">A00</column> |</row>
|
|The length |attribute for the REC2XML call (see below) with an expansion factor of |1.0 would be 128 (11 for the '<row>' and '</row>' overhead, |21 for the column names, 75 for the '<column name=', '>', '</column>' |and double quotes, 7 for the CLASS_CODE data, 6 for the DAY data, and |8 for the STARTING data). Since the '&' and '<' characters will |be replaced, an expansion factor of 1.0 will not be sufficient. The length |attribute of the function will need to support an increase from 7 |to 14 characters for the new format CLASS_CODE data.
|However, since |it is known that the DAY value will never be more than 1 digit long, an |extra 5 is calculated into the length that will never be used. Therefore, |the expansion only needs to handle an increase of 2. Since CLASS_CODE |is the only character string column in the argument list, this is the |only column value to which the expansion factor applies. To get an increase |of 2 for the length, an expansion factor of 9/7 (approximately 1.2857) |would be needed. An expansion factor of 1.3 will be used.
| SELECT REC2XML (1.3, 'COLATTVAL', 'record', CLASS_CODE, DAY, STARTING) | FROM CL_SCHED | WHERE CLASS_CODE = '&43<FIE'
|Returns the following string:
| <record> | <column name="CLASS_CODE">&43<FIE</column> | <column name="DAY">5</column> | <column name="STARTING">06:45:00</column> | </record>
|
| SELECT REC2XML (1.3,'COLATTVAL', '', Class, "time<noon") | FROM (SELECT Class_code, Starting | FROM Cl_sched | WHERE Starting < '12:00:00') | AS Early (Class, "time<noon")|This |query returns the following string:
| <row> | <column name="CLASS">&43<FIE</column> | <column name="time<noon>06:45:00</column> | </row>
|
|SELECT REC2XML (1.0,'COLATTVAL_XML', '', EMPNO, RESUME_XML) | FROM (SELECT EMPNO, CAST(RESUME as VARCHAR(3500)) as RESUME_XML | FROM EMP_RESUME | WHERE RESUME_FORMAT = 'XML') | AS EMP_RESUME_XML|
|>>-ROUND---(expression1, expression2)-------------------------->< |
|The schema is SYSIBM.
|This function was first available in FixPak 2 of Version 7.1.
|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. |
|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.
|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
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
|>>-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: |
|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
|>>-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: |
|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
|>>-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. |
|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).
|>>-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. |
|
|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.
|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.