DB2

A Friend asked how to convert multi-row to one row that have multi-column with delimiter in DB2. Such as:

c1
----
a
b
c

==>

c2
----------
a | b | c

Actually, There are 2 different requirements on this point. The first one is that the multi-row number is limited and could be referred as a flag. Such as:

c1        c2
----      ----
10        Jan
21         Feb
88         Mar
...         ...
04         Dec

The second one is that the multi-row number is unlimited or can not be referred with any character.

There's the way that uses pure SQL to implement the first requirement. Here's a sample: changing row to column

select
emp_id,
sum(case month(pay_date)
 when 1 then salary
 else 0
end) Jan,
sum(case month(pay_date)
 when 2 then salary
 else 0
end) Feb,
sum(case month(pay_date)
 when 3 then salary
 else 0
end) Mar,
sum(case month(pay_date)
 when 4 then salary
 else 0
end) Apr,
sum(case month(pay_date)
 when 5 then salary
 else 0
end) May,
sum(case month(pay_date)
 when 6 then salary
 else 0
end) Jun,
sum(case month(pay_date)
 when 7 then salary
 else 0
end) Jul,
sum(case month(pay_date)
 when 8 then salary
 else 0
end) Aug,
sum(case month(pay_date)
 when 9 then salary
 else 0
end) Sep,
sum(case month(pay_date)
 when 10 then salary
 else 0
end) Oct,
sum(case month(pay_date)
 when 11 then salary
 else 0
end) Nov,
sum(case month(pay_date)
 when 12 then salary
 else 0
end) Dec,
sum(salary) total
from emp_salary
group by emp_id 

To the 2nd requirement, the only way is to write use-defined function for it. It accepts a cursor and output one row. Here's a sample in Oracle.

SELECT 
a.deptno, 
a.dname, 
a.loc, rowtocol('SELECT DISTINCT job 
FROM emp WHERE deptno = ' ||a.deptno) as jobs 
FROM dept a;

CREATE OR REPLACE FUNCTION rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' ) 
RETURN VARCHAR2 AUTHID CURRENT_USER AS ...    
TYPE c_refcur IS REF CURSOR;
lc_str VARCHAR2(4000);     
lc_colval VARCHAR2(4000);     
c_dummy c_refcur;     
l number;     
BEGIN
OPEN c_dummy FOR p_slct;     
LOOP
FETCH c_dummy INTO lc_colval;     
EXIT WHEN c_dummy%NOTFOUND;     
lc_str := lc_str || p_dlmtr || lc_colval;     
END LOOP;     
CLOSE c_dummy;     
RETURN SUBSTR(lc_str,2);   
/*      
EXCEPTION      
WHEN OTHERS THEN     
lc_str := SQLERRM;     
IF c_dummy%ISOPEN THEN     
CLOSE c_dummy;     
END IF;     
RETURN lc_str;     
*/
END;

Update 2008-08-01 

A little update on the topic.

What if when the function SUM() is not allowed for the data type? See below example:

with tmp(c1,c2,c3) as (
values (1,'a',1), (1,'b',2)
)
select * from tmp
@

C1          C2 C3         
----------- -- -----------
1 a            1
1 b            2
2 record(s) selected.

The answer is we could use MAX(), MIN() like below:

select 
c1, 
max(case c3 when 1 then c2 end), 
max(case c3 when 2 then c2 end) 
from tmp group by c1@ 

 C1          2 3
----------- - -
1 a b
1 record(s) selected.

This is a limitation in db2 V8.2.x and any less version.

The limitation is removed since DB2 V9.0 is released.

Note: If you want to create SPs whose length is bigger than 65535, you mush make both the server and client are V9.0 or higher.

The error could be caused when you are calling a function which has parameters as CHARACTER with following style:

db2 values test_func('test')

the function has following defination

test_func(input character(4))

To avoid the error, you need to make a convertion for the input parameter, like this:

db2 values test_func(char('test'))

CREATE PROCEDURE usp1 (
	OUT  poReturnStat INTEGER
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN NOT ATOMI 
     	DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
            	SELECT 'aa' AS procr_code
            	FROM sysibm.sysdummy1;

	OPEN cursor1;
	SET  poReturnStat = 0;
END
@

There's an 18 characters length limited on the specific of stored procedure.

CREATE PROCEDURE usp1 
LANGUAGE SQL
DYNAMIC RESULT SETS 1
SPECIFIC aaaaabbbbbcccccddde

SQL0107N  名称 "AAAAABBBBBCCCCCDDDE" 太长。最大长度是 "18 

NOTE: this limitation is not effected with procedure names.

2006-6-2 Update

NOTE: this limitation is effected with both Names and Specifics for functions.

$ db2 create table zytst.t1 like zytst.v1@
$ db2 describe table zytst.t1@

Get an error as compiling a view.

SQL1585N  不存在具有足够页大小的系统临时表空间。  SQLSTATE=54048 

SQL1585N不存在具有足够页大小的系统临时表空间。

解释:

可能发生了下列其中一种情况：

1.    系统临时表的行长度超过了数据库中最大系统临时表空间中可接受的限
    制。

2.    系统临时表中所需的列数超过了数据库中最大系统临时表空间中可接受
    的限制。

系统临时表空间限制取决于其页大小。这些值是：


  最大         最大   临时
  记录         列数   表空间的
  长度                页大小
  -----------  ----  ------------
  1957  字节   244   2K
  4005  字节   500   4K
  8101  字节   1012  8K
  16293 字节   1012  16K
  32677 字节   1012  32K

The view being compiled is based on other view which contain a function that has the return parameter VARCHAR(32672).

SQLLIB\BIN> db2 list tablespaces show detail

表空间标识                        = 1
名称                              = TEMPSPACE1
类型                              = 系统管理空间
内容                              = 系统临时数据
......
页大小（以字节计）                = 4096
......

I tried to reduce the return parameter length down to 4005 in the function, but the error continues to happen as compiling the view. Finally it works for VARCHAR(3600) as maximum.

Please see following test case.

CREATE FUNCTION zytst.fun1
RETURNS VARCHAR(32672)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
RETURN 'abc'
@

CREATE VIEW zytst.v1 AS
VALUES zytst.fun1() AS c1
@ 

CREATE VIEW zytst.v2 AS
SELECT c1 AS c1 FROM zytst.v1
@ 

See following SQL

SELECT DISTINCT c1 FROM t1
UNION
SELECT c1 FROM t2
@ 

We could remove DISTINCT safely. UNION will do what DISTINCT does here. See following sample.

SELECT c1 FROM t1@

c1
----
1
1
2

SELECT c1 FROM t2@

c1
----
2
3
3 

SELECT c1 FROM t1
UNION
SELECT c1 FROM t2
@ 

c1
----
1
2
3

SELECT * FROM TABLE( schemaName.functionName(parameters) ) AS t1

Note: It does Not work as following style.

SELECT * FROM ( TABLE( schemaName.functionName(parameters) ) ) AS t1

The complex CASE WHEN in fullselect statement would reduce performance much. We could call SQL Score functions instead of the place of CASE WHEN, it could be 15X improved in following example.

NOTE: However, CASE WHEN is better than IF ELSE which is slower. But you can not use CASE WHEN END; in functions. It reports unrelated and strange error as compiling.

继续阅读 "CASE WHEN in views reduces performance" 的剩余内容

following code would be fine:

declare var1 datatype;
declare mycur cursor for
    select * from table1
    where column = var1;

set var1 = 'varlue';
open mycur;

the results would be limited with the condition "where column = var1" as your wish.

字段定义有default 值并不代表可以省略not null定义，可如下测试：

create table zytest(a int default 9);
insert into zytest values(default);
insert into zytest values(1);
insert into zytest values(null);

select * from zytest;

a
----
9
-
1

update zytest set a = null where a = 1;

select * from zytest;

a
----
9
-
-

update zytest set a = default where a is null;

select * from zytest;

a
----
9
9
9

dos> db2 connect to db_name user xxx using ***

SQL0332N 没有从源代码页“819”至目标代码页“1386”的转换，原因码是“1”
SQLSTATE=57017

原因：连接的数据库使用的代码页设置（819）与本地客户端设置的代码页（1386）不同 之间无法进行双向转换，即无法将GBK 编码的字符转换为ISO-8859-1字符，解决方法：

dos> db2set DB2CODEPAGE=819
dos> db2 terminate
dos> db2 connect to db_name user xxx using ***
连接成功。

如果数据库是UTF-8 encoded，本地客户端的代码页是1386，则可以正常连接，不存在此问题。

对于本地的代码页似乎只能设置一个值
，因此如果需要同时连接多个具有不同代码页的数据库，就会比较麻烦，无法做到真正的同时连接，只能每次使用上面的命令切换。

SQL0181N  日期时间值的字符串表示法超出范围。  SQLSTATE=22007 

I got this error when I try to insert following timestamp.

'2006-02-29-01.04.00.000000'

create view zytst.v as
select (case t.c1 when ... then ... end) as vc1,
    (case vc1 when ... then ... end) as vc2
from zytst.t
@

this statement does not work, because vc1 is an unavailable reference. Use following code instead.

create view zytst.v as
with tmp as
(select (case t.c1 when ... then ... end) as vc1
from zytst.t)

select vc1 as vc1,
    (case vc1 when ... then ... end) as vc2
from tmp
@

In some special case we could use ORDER BY clause instead of using MAX and MIN function. it makes the SQL more simple.

See following sample, the emphasis line is the row wanted.

select * from zytst.t@

part_num    group    plan_date
---------------------------------------
ADB               1            2006-02-12
ABB               1            2006-02-13
ACB               1            2006-02-13
AGB               2            2006-02-16

Following 2 SQLs get the same result in this case.

select part_num from zytst.t where group = 1
order by plan_date desc, part_num desc
fetch first 1 row only@ 

part_num    group    plan_date
---------------------------------------
ACB               1            2006-02-13

select max(part_num) 
from (select t1.part_num from zytst.t t1
        where t1.group = 1    
        and t1.plan_date =
            （select max(plan_date) from zytst.t t2
             where t2.group = t1.group) ) as t3
@

part_num    group    plan_date
---------------------------------------
ACB               1            2006-02-13 

CASE 

can not use CASE WHEN statement within

FOR myloop AS 
SELECT c1 FROM t1
DO
 ... 
END FOR;

use IF ELSEIF instead.

SELECT INTO 

can not use SELECT INTO statement within

FOR myloop AS 
SELECT c1 FROM t1
DO
 ... 
END FOR;

use SET var = (SELECT ... FROM ...) instead.

COLUMN 

can not refer to the column name c1 as t1.c1 within

FOR ... AS SELECT c1 FROM tab AS t1

can only use c1 without schema.

Problem Description 

There's no any response for creating or droping any procedures in DB2. Seems it's pending there, without any error returned. This time UPDATE statement on tables could be issued successfully.

Check Process

Check OS disk available

$ df -kl 

Check database configure

db2 => get db cfg |more 

Check system catalog tablespace 

db2 => list tablespaces show detail |more
syscatspace	0x0000 

Cause 

It caused by issuing CREATE or DROP statement without COMMIT or ROLLBACK statement in CLP which is in non-Autocommit mode.

Solution 

After you issue a COMMIT or a ROLLBACK in the CLP or close it, the pending is gone.

We must be very careful when we use client in non-Autocommit mode just like CLPs in this case.

To check the Autocommit mode in CLP issues. Refer to:

set COMMIT mode in db2 clp

db2 => list command options

 -c    自动落实                                  OFF 

More 

From this point, we could get more. Most of strange things like this(pending there and no error return) are caused by locking.

In this case, it was only the opertation on procedures being pended, so track on this thread to suppose the syscat tables related with procedures are locked then find the what probably causes it.
 

select procname, valid from syscat.procedures where procschema = upper('zytst')

a procedure A is called in another procedure B, then says A is depended by B, or B depends A.

When we rebuild procedure A, B will not work untill you rebuild B too after A was rebuilt.

See following test case.

db2 => create procedure zytst.a() begin return 0; end
db2 => create procedure zytst.b() begin call zytst.a; return 0; end
db2 => select procname, valid from syscat.procedures where procschema = upper('zytst')

PROCNAME	VALID
------------------------
A			Y
B			Y
 db2 => call zytst.a

return status = 0
 db2 => call zytst.b

return status = 0

db2 => drop procedure zytst.a
db2 => create procedure zytst.a() begin return 0; end db2 => select procname, valid from syscat.procedures where procschema = upper('zytst')

PROCNAME	VALID
------------------------
A			Y
B			Y

db2 => call zytst.b

SQL0440N  未找到类型为 "PROCEDURE" 命名为 "ZYTST.A"
且具有兼容自变量的已授权例程。  SQLSTATE=42884
 db2 => drop procedure zytst.b
db2 => create procedure zytst.b() begin call zytst.a; return 0; end
 
db2 => call zytst.b

return status = 0

Reference

• Retrieving subtype data from DB2
  http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/t0006649.htm?resultof=%22%66%75%6e%63%74%69%6f%6e%22%20%22%69%6e%70%75%74%22%20%22%70%61%72%61%6d%65%74%65%72%22%20%22%70%61%72%61%6d%65%74%22%20%22%74%61%62%6c%65%22%20%22%74%61%62%6c%22%20
• Returning subtype data to DB2
  http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/t0006651.htm?resultof=%22%66%75%6e%63%74%69%6f%6e%22%20%22%69%6e%70%75%74%22%20%22%70%61%72%61%6d%65%74%65%72%22%20%22%70%61%72%61%6d%65%74%22%20%22%74%61%62%6c%65%22%20%22%74%61%62%6c%22%20
  

 Common usage

   CREATE PROCEDURE create_dept_table 
   (IN deptNumber VARCHAR(3), OUT table_name VARCHAR(30))
   LANGUAGE SQL
     BEGIN
       DECLARE stmt VARCHAR(1000);

       -- continue if sqlstate 42704 ('undefined object name')
       DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
         SET stmt = '';
       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
         SET table_name = 'PROCEDURE_FAILED';

       SET table_name = 'DEPT_'||deptNumber||'_T';
       SET stmt = 'DROP TABLE '||table_name;
       PREPARE s1 FROM stmt;
       EXECUTE s1;
       SET stmt = 'CREATE TABLE '||table_name||
        '( empno CHAR(6) NOT NULL, '||
        'firstnme VARCHAR(12) NOT NULL, '||
        'midinit CHAR(1) NOT NULL, '||
        'lastname VARCHAR(15) NOT NULL, '||
        'salary DECIMAL(9,2))';
       PREPARE s2 FROM STMT;
       EXECUTE s2;
       SET stmt = 'INSERT INTO '||table_name || ' ' ||
        'SELECT empno, firstnme, midinit, lastname, salary '||
        'FROM employee '||
        'WHERE workdept = ?';
      PREPARE s3 FROM stmt;
      EXECUTE s3 USING deptNumber;
   END 

As Cursor

DECLARE mycur CURSOR FOR mystmt;
DECLARE str_sql VARCHAR(256);

SET str_sql = 'SELECT c1, ''string'' FROM t1 WHERE c2 =  ''A'''

PREPARE mystmt FROM str_sql;
OPEN mycur;

Multi-parameter

DECLARE var1 CHAR(1);
DECLARE var2 CHAR(1);
DECLARE str_sql VARCHAR(256);

SET str_sql = 'SELECT c1, ''string'' FROM t1 WHERE c2 =  ? AND c3 = ?'

PREPARE s1 FROM str_sql;
EXECUTE s1 USING var1, var2;

继续阅读 "Prepare and Execute Dynamic SQL" 的剩余内容

Latin-1 characters (0x80 - 0xFF) are encoded as two-byte by UTF-8.

In some case, we might need to translate the Latin-1 characters encoded by UTF-8 back to one-byte.

First we need to know how UTF-8 encodes the Latin-1 from one-byte to two-byte. All two-byte UTF-8 characters have the following fixed encoding format. the value of 'x' depends the character being encoded.

110x,xxxx 10xx,xxxx 

In the first byte, the first two '11' means that this is a two byte character. the closest followed '0' is a fixed flag for spliting the first two flag bits with the rest bits.

In the second byte, the first '10' is fixed flag to make a difference with the leading byte, for example, 110x,xxxx is a leading byte for a character. All ASCII characters is leading by a 0xxx,xxxx, so if the application reads a byte like 10xx,xxxx and can not read the byte before it, then it can abandon this byte to read next.

For an ASCII encoded Latin-1 character 0xC1, its binary encoding is:

11000001

Fowlling shows how it maps to the two-byte in UTF-8 encoding.

11000011 10000001 

110 and 10 is fix flag.

000 is the fillers.

Way one 

Following is the implement by DB2 SQL PL.

SET h_s = SUBSTR( str, i, 1 ) ;
SET l_s = SUBSTR( str, i + 1, 1 ) ;

SET h_s = CHR( MOD( ASCII( h_s ) * 64, 256 ) ) ;
SET l_s = CHR( MOD( ASCII( l_s ) * 4, 256 ) / 4 ) ;

RETURN CHR( ASCII( h_s ) + ASCII( l_s ) ) ;

In the code above, we use the operators *(multiple), /(divide), +(plus) and 'mod' instead of the bit-operators in C language, <<, >> and 'or'. It's the same with following C code.

h_s = h_s << 6 ;
l_s = l_s << 2 ;
l_s = l_s >> 2 ;

return h_s or l_s ;

Way two 

SET h_s = SUBSTR( str, i, 1 ) ;
SET l_s = SUBSTR( str, i+1, 1 ) ;

ELSEIF (h_s = 194) THEN -- latin-1
	-- 0xC2 0x80 - 0xC2 0xBF ==> 0x80 - 0xBF
	RETURN l_s ;

ELSEIF (h_s = 195) THEN -- latin-1
	-- 0xC3 0x80 - 0xC3 0xBF ==> 0xC0 - 0xFF, 0xC0 - 0x80 = 0x40(64)
	RETURN CHR( ASCII( l_s ) + 64 ) ;

END IF;

Refer to <SQL Reference Volume 1>  at page 510 in section of Select-statement.

Basic Using 

CREATE TABLE PARTLIST 
	(PART VARCHAR(8), 
	 SUBPART VARCHAR(8), 
	 QUANTITY INTEGER);

WITH RPL (PART, SUBPART, QUANTITY) AS 
	( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY 
	  FROM PARTLIST ROOT 
	  WHERE ROOT.PART = ’01’

	  UNION ALL 
	  SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY 
	  FROM RPL PARENT, PARTLIST CHILD 
	  WHERE PARENT.SUBPART = CHILD.PART 
	) 
SELECT DISTINCT PART, SUBPART, QUANTITY 
  FROM RPL 
    ORDER BY PART, SUBPART, QUANTITY; 

Using nested WITH

WITH v1 AS
	( SELECT c1 FROM t1 ),
v2 AS
	( SELECT c1 FROM v1 ), 
v3 AS
	( SELECT c1 FROM v2 )

SELECT c1 FROM v3
@ 

There's 4 isolation levels in db2.

1. uncommitted read  (UR)
   
2. cursor stability (CS)
3. read stability (RS)
4. repeatable read (RR)
   

example:

SELECT * FROM TEST
WITH UR

DECLARE mycur CURSOR FOR
SELECT * FROM TEST
WITH UR

Can't specify isolation in the ddl of a view.

The following syntax is also supported:

• UR - READ UNCOMMITTED
• UR - DIRTY READ
• CS - READ COMMITED 
• CS - CURSOR STABILITY
• RR - REPEATABLE READ
• RR - SERIALIZABLE