DSNTESU
Test cases for Db2 Sample User Defined Functions (UDFs) Processed by Db2 Sample job DSNTEJ2U .
----------------------------------------------------------------------- 00001000
-- NAME = DSNTESU 00002000
-- 00003000
-- DESCRIPTIVE NAME = SAMPLE EXPLAIN TABLES 00004000
-- 00005000
-- LICENSED MATERIALS - PROPERTY OF IBM 00006000
-- 5615-DB2 00007000
-- (C) COPYRIGHT 1982, 2013 IBM CORP. ALL RIGHTS RESERVED. 00008000
-- 00009000
-- STATUS = VERSION 11 00009100
-- 00009200
-- FUNCTION = Test cases for DB2 Sample User Defined Functions (UDFs) 00009300
-- Processed by DB2 Sample job DSNTEJ2U 00020000
-- 00020100
-- NOTES: 00021000
-- 00026000
-- CHANGE LOG: 00027000
-- 11/07/2012 Add prolog dn1651_inst1 / dn1651 00029000
-- 05/17/2013 FIX COPYRIGHT STATEMENT 49779_077_724 00029100
-- 00029200
-- ******************************************************************* 00040000
-- Use ALTDATE to get the current date as 'DD MONTH YY' (day of the 00050000
-- month with leading zero retention, name of the month, and year 00060000
-- without century). 00070000
-- ******************************************************************* 00080000
SELECT DSN8.ALTDATE( 'DD MONTH YY' ) 00090000
FROM SYSIBM.SYSDUMMY1; 00100000
00110000
-- Release DB2 resources 00120000
ROLLBACK; 00130000
00140000
-- ******************************************************************* 00150000
-- Use ALTDATE to get the current date as 'D.M.YYYY' (day of the month 00160000
-- with leading zero suppression, month of the year with leading zero 00170000
-- suppression, and year with century). 00180000
-- ******************************************************************* 00190000
SELECT DSN8.ALTDATE( 'D.M.YYYY' ) 00200000
FROM SYSIBM.SYSDUMMY1; 00210000
00220000
-- Release DB2 resources 00230000
ROLLBACK; 00240000
00250000
-- ******************************************************************* 00260000
-- Use ALTDATE to get the current date as 'YYYY-XX-DD' (year with 00270000
-- century, month of the year as a roman numeral, day of the month 00280000
-- with zero retention). 00290000
-- ******************************************************************* 00300000
SELECT DSN8.ALTDATE( 'YYYY-XX-DD' ) 00310000
FROM SYSIBM.SYSDUMMY1; 00320000
00330000
-- Release DB2 resources 00340000
ROLLBACK; 00350000
00360000
-- ******************************************************************* 00370000
-- Use ALTDATE to convert a date from 'DD MONTH YYYY' to 'YYYY/MM/DD' 00380000
-- ******************************************************************* 00390000
SELECT DSN8.ALTDATE( '11 November 1918', 00400000
'DD MONTH YYYY', 00410000
'YYYY/MM/DD' ) 00420000
FROM SYSIBM.SYSDUMMY1; 00430000
00440000
-- Release DB2 resources 00450000
ROLLBACK; 00460000
00470000
-- ******************************************************************* 00480000
-- Use ALTDATE to convert from ISODATE format to 'D.M.YY' format 00490000
-- ******************************************************************* 00500000
SELECT FIRSTNME || ' ' 00510000
|| LASTNAME || ' was hired on ' 00520000
|| DSN8.ALTDATE( CHAR(HIREDATE,ISO), 00530000
'YYYY-MM-DD', 00540000
'D.M.YY' ) 00550000
FROM EMP 00560000
WHERE EMPNO = '000130'; 00570000
00580000
-- Release DB2 resources 00590000
ROLLBACK; 00600000
00610000
-- ******************************************************************* 00620000
-- Use ALTTIME to get the current time in 12-hour clock format, with- 00630000
-- out seconds 00640000
-- ******************************************************************* 00650000
SELECT DSN8.ALTTIME( 'H:MM AM/PM' ) 00660000
FROM SYSIBM.SYSDUMMY1; 00670000
00680000
-- Release DB2 resources 00690000
ROLLBACK; 00700000
00710000
-- ******************************************************************* 00720000
-- Use ALTTIME to get the current time in 24-hour clock format, with- 00730000
-- out seconds 00740000
-- ******************************************************************* 00750000
SELECT DSN8.ALTTIME( 'HH.MM' ) 00760000
FROM SYSIBM.SYSDUMMY1; 00770000
00780000
-- Release DB2 resources 00790000
ROLLBACK; 00800000
00810000
-- ******************************************************************* 00820000
-- Use ALTTIME to get the current time in 24-hour clock format, with 00830000
-- seconds 00840000
-- ******************************************************************* 00850000
SELECT DSN8.ALTTIME( 'HH:MM:SS' ) 00860000
FROM SYSIBM.SYSDUMMY1; 00870000
00880000
-- Release DB2 resources 00890000
ROLLBACK; 00900000
00910000
-- ******************************************************************* 00920000
-- Use ALTTIME to convert from 24-hour clock format with seconds to 00930000
-- 12-hour clock format, without seconds 00940000
-- ******************************************************************* 00950000
SELECT DSN8.ALTTIME( '00:00:00','HH:MM:SS','HH:MM AM/PM' ) 00960000
FROM SYSIBM.SYSDUMMY1; 00970000
00980000
-- Release DB2 resources 00990000
ROLLBACK; 01000000
01010000
-- ******************************************************************* 01020000
-- Use ALTTIME to convert from 24-hour clock format with seconds to 01030000
-- 12-hour clock format, without seconds and with supression of any 01040000
-- leading zero on the hour 01050000
-- ******************************************************************* 01060000
SELECT DSN8.ALTTIME( '06.42.37','HH.MM.SS','H:MM AM/PM' ) 01070000
FROM SYSIBM.SYSDUMMY1; 01080000
01090000
-- Release DB2 resources 01100000
ROLLBACK; 01110000
01120000
-- ******************************************************************* 01130000
-- Use ALTTIME to convert from ISOTIME to 12-hour clock format, with- 01140000
-- out seconds and with retention of any leading zero on the hour. 01150000
-- ******************************************************************* 01160000
SELECT DSN8.ALTTIME( CHAR(CURRENT TIME,ISO), 01172900
'HH.MM.SS','HH:MM AM/PM' ) 01175900
FROM SYSIBM.SYSDUMMY1; 01180000
01190000
-- Release DB2 resources 01200000
ROLLBACK; 01210000
01220000
-- ******************************************************************* 01230000
-- Use CURRENCY to express an amount in US dollars with bank-style 01240000
-- credit/debit indicators. 01250000
-- ******************************************************************* 01260000
SELECT DSN8.CURRENCY( -1234.56,'$','CR/DB' ) 01270000
FROM SYSIBM.SYSDUMMY1; 01280000
01290000
-- Release DB2 resources 01300000
ROLLBACK; 01310000
01320000
-- ******************************************************************* 01330000
-- Use CURRENCY to express an amount in Deutsche Marks with accounting 01340000
-- style credit/debit indicators. 01350000
-- ******************************************************************* 01360000
SELECT DSN8.CURRENCY( -1234.56,'DM','(/)' ) 01370000
FROM SYSIBM.SYSDUMMY1; 01380000
01390000
-- Release DB2 resources 01400000
ROLLBACK; 01410000
01420000
-- ******************************************************************* 01430000
-- Use CURRENCY to express an amount in Canadian dollars with 01440000
-- arithmetic-style credit/debit indicators. 01450000
-- ******************************************************************* 01460000
SELECT DSN8.CURRENCY( -1234.56,'CD','+/-' ) 01470000
FROM SYSIBM.SYSDUMMY1; 01480000
01490000
-- Release DB2 resources 01500000
ROLLBACK; 01510000
01520000
-- ******************************************************************* 01530000
-- Use DAYNAME to get the current day of the week 01540000
-- ******************************************************************* 01550000
SELECT DSN8.DAYNAME( CHAR(CURRENT DATE,ISO) ) 01560000
FROM SYSIBM.SYSDUMMY1; 01570000
01580000
-- Release DB2 resources 01590000
ROLLBACK; 01600000
01610000
-- ******************************************************************* 01620000
-- Use DAYNAME to get the day of the week of the millenial leap year 01630000
-- ******************************************************************* 01640000
SELECT DSN8.DAYNAME( '2000-02-29' ) 01650000
FROM SYSIBM.SYSDUMMY1; 01660000
01670000
-- Release DB2 resources 01680000
ROLLBACK; 01690000
01700000
-- ******************************************************************* 01710000
-- Use DAYNAME to get the day of the week on which Delores Quintana 01720000
-- was hired 01730000
-- ******************************************************************* 01740000
SELECT FIRSTNME || ' ' 01750000
|| LASTNAME || ' was hired on ' 01760000
|| DSN8.DAYNAME( CHAR(HIREDATE,ISO) ) || ', ' 01770000
|| CHAR( HIREDATE ) 01780000
FROM EMP 01790000
WHERE EMPNO = '000130'; 01800000
01810000
-- Release DB2 resources 01820000
ROLLBACK; 01830000
01840000
-- ******************************************************************* 01850000
-- Use MONTHNAME get the name of the current month 01860000
-- ******************************************************************* 01870000
SELECT DSN8.MONTHNAME( CHAR(CURRENT DATE,ISO) ) 01880000
FROM SYSIBM.SYSDUMMY1; 01885000
01890000
-- Release DB2 resources 01900000
ROLLBACK; 01910000
01920000
-- ******************************************************************* 01930000
-- Use MONTHNAME to get the name of the month in which Delores Quin- 01940000
-- tana was hired 01950000
-- ******************************************************************* 01960000
SELECT FIRSTNME || ' ' 01970000
|| LASTNAME || ' was hired in the month of ' 01980000
|| DSN8.MONTHNAME( CHAR(HIREDATE,ISO) ) 01990000
FROM EMP 02000000
WHERE EMPNO = '000130'; 02010000
02020000
-- Release DB2 resources 02030000
ROLLBACK; 02040000
02050000
-- ******************************************************************** 02060000
-- Uncomment the following to drop test objects, which are required to 02070000
-- demonstrate the TABLE_NAME, TABLE_SCHEMA, and TABLE_LOCATION UDFs. 02080000
-- ******************************************************************** 02090000
-- EXEC SQL DROP VIEW DSN8.VIEW_OF_SYSTABLES; 02100000
-- EXEC SQL DROP ALIAS DSN8.ALIAS_OF_VIEW; 02110000
-- EXEC SQL DROP ALIAS DSN8.ALIAS_OF_SYSTABLES; 02120000
-- EXEC SQL DROP ALIAS DSN8.ALIAS_RS_SYSTABLES; 02130000
-- EXEC SQL COMMIT; 02140000
02150000
-- ******************************************************************** 02160000
-- Create a view of SYSIBM.SYSTABLES 02170000
-- ******************************************************************** 02180000
CREATE VIEW DSN8.VIEW_OF_SYSTABLES 02190000
AS SELECT * 02200000
FROM SYSIBM.SYSTABLES; 02210000
-- ******************************************************************** 02220000
-- Create an ALIAS of the VIEW of SYSIBM.SYSTABLES 02230000
-- ******************************************************************** 02240000
CREATE ALIAS DSN8.ALIAS_OF_VIEW 02250000
FOR DSN8.VIEW_OF_SYSTABLES; 02260000
-- ******************************************************************** 02270000
-- Create an ALIAS of SYSIBM.SYSTABLES 02280000
-- ******************************************************************** 02290000
CREATE ALIAS DSN8.ALIAS_OF_SYSTABLES 02300000
FOR SYSIBM.SYSTABLES; 02310000
-- ******************************************************************** 02320000
-- Create an ALIAS of SYSIBM.SYSTABLES at a "remote" server 02330000
-- ******************************************************************** 02340000
CREATE ALIAS DSN8.ALIAS_RS_SYSTABLES 02350000
FOR REMOTE_SITE.SYSIBM.SYSTABLES; 02360000
02370000
COMMIT; 02380000
02390000
-- ******************************************************************** 02400000
-- Set the current SQLID to the sample schema 02410000
-- ******************************************************************** 02420000
SET CURRENT SQLID = 'DSN8'; 02430000
02440000
-- ******************************************************************** 02450000
-- Use TABLE_NAME to get the name of the base object for ALIAS_OF_VIEW 02460000
-- (which is VIEW_OF_SYSTABLES) 02470000
-- ******************************************************************** 02480000
SELECT DSN8.TABLE_NAME( 'ALIAS_OF_VIEW' ) 02490000
FROM SYSIBM.SYSDUMMY1; 02500000
02510000
-- Release DB2 resources 02520000
ROLLBACK; 02530000
02540000
-- ******************************************************************** 02550000
-- Use TABLE_SCHEMA to get the name of the base schema for ALIAS_OF_ 02560000
-- SYSTABLES (which is SYSIBM) 02570000
-- ******************************************************************** 02580000
SELECT DSN8.TABLE_SCHEMA( 'ALIAS_OF_SYSTABLES' ) 02590000
FROM SYSIBM.SYSDUMMY1; 02600000
02610000
-- Release DB2 resources 02620000
ROLLBACK; 02630000
02640000
-- ******************************************************************** 02650000
-- Use TABLE_LOCATION to get the name of the location where the base 02660000
-- object for ALIAS_RS_SYSTABLES resides (REMOTE_SITE) 02670000
-- ******************************************************************** 02680000
SELECT DSN8.TABLE_LOCATION( 'ALIAS_RS_SYSTABLES' ) 02690000
FROM SYSIBM.SYSDUMMY1; 02700000
02710000
-- Release DB2 resources 02720000
ROLLBACK; 02730000
02740000
-- ******************************************************************** 02750000
-- Drop the test objects that were used to demonstrate the TABLE_NAME, 02760000
-- TABLE_SCHEMA, and TABLE_LOCATION UDFs. 02770000
-- ******************************************************************** 02780000
DROP VIEW DSN8.VIEW_OF_SYSTABLES; 02790000
DROP ALIAS DSN8.ALIAS_OF_VIEW; 02800000
DROP ALIAS DSN8.ALIAS_OF_SYSTABLES; 02810000
DROP ALIAS DSN8.ALIAS_RS_SYSTABLES; 02820000
COMMIT; 02830000