[DB2] Frequently used command (update from time to time)

db2 set schema gmp
Tell the system we are using schema gmp, so next time we can just type the table name.
e.g. select * from tb_user

db2 list node directory

db2 list database show detail

connect to [dbname] user [username] using [password]

select row_number() over( a.name ) r from table a where a.id = ?

How to identify unicode characters in DB2?

length( str )
returns the byte length of [str]

e.g. select length('陳大文') from sysibm.sysdummy1;
Result is 9 because each Chinese character occupies 3 bytes.

substr( str, [start], [length] )
returns the sub string of length [length] in [str] from the [start] byte.

e.g. select substr( 'apple', 2, 2 ) from sysibm.sysdummy1;
returns "pp"

Warning!!! Substr() is not appropriate for strings containing unicode characters

character_length( str, [encoding] )
returns the character length of [str]

e.g. select character_length('陳大文', CODEUNITS32) from sysibm.sysdummy1;
Result is 9 because each Chinese character occupies 3 bytes.

substring( str, [start], [length], [encoding] )
returns the sub string of length [length] in [str] from the [start] byte.

e.g. select substring('陳大文', 2, 1, CODEUNITS32) from sysibm.sysdummy1;
returns "大"

If you want to go through each character of a string, remember to use character_length( str, [encoding] ) to count the number of character first, then for each character, use length() to check if it is a unicode character, thanks.

Avoid using "not exist", use "minus" if possible

Using "NOT IN"
select * from table a where a.id not in (1,2,3)

Same result using "minus"

select * from table a
minus
select * from table a where a.id in (1,2,3)

Export csv

export to c:\abc\table.csv of del
select * from table a

Check tables / columns / indexes / procedures

select * from syscat.tables where tabname like 'ABC%'

select * from syscat.columns where tabname like 'ABC%' and colname like 'ABC%'

select * from syscat.indexes where ...

select * from syscat.procedures where procname like 'ABC%'

select * from syscat.functions where funcname like 'ABC%'