pajama

For some reasons, some packages or routines may be marked as INVALID. For package which is invalid, it will allow revalidation to occur implicitly when the package is first used, or to occur explicitly by either the REBIND or the BIND command.
The rebind_rouine_package function will rebind the package associate with the SQL procedure, it will mark the related package itself VALID as well.
-> 해당 루틴이 VALID인지는 syscat.routines 카탈로그 테이블을 참조하면 됩니다. 'N'이면 INVALID 상태입니다.

However it is not the case for routine, it could not mark the routine VALID by REBIND, BIND command, and the rebind_rouine_package function.

As REBIND validate the package only, for routine to be VALID, it needs to revalidate it implicitly by accessing it, or call function such as admin_revalidate_db_objects explicitly.

For example: call sysproc.admin_revalidate_db_objects('PROCEDURE','MY_SCHEMA','MY_PROCEDURE')
-> 특정 프로시저나 함수이름을 지정할 수 있고, 특정 스키마에 대한 일괄 수행을 하려면 NULL을 씁니다.

It is different to revalidate objects that are inside of a module,
If the routine is within one module, it needs to use the MODULE type with the name of a specific module in ADMIN_REVALIDATE_DB_OBJECTS call,
and all of the invalid objects include routines inside of that module will be revalidated.

Such as the routine SYSIBMADM.WRITE_BLOB could NOT be revalidated by:
call sysproc.admin_revalidate_db_objects('PROCEDURE','SYSIBMADM','WRITE_BLOB').

Instead, as it belongs to UTL_MAIL module, it needs to be revalidated by:
call sysproc.admin_revalidate_db_objects('MODULE','SYSIBMADM','UTL_MAIL').

No connections can be made to the database when userid and password are provided. These connections fail with SQL30082N with Reason "24" or "15" returned.

DB2 Version 9.1 and Version 9.5 up to Fixpack 3 support the following encryption algorithms.

Crypt 
MD5 
SHA1

From DB2 Version 9.5 Fixpak 4 and versions beyond, the following algorithms are supported:

Crypt 
MD5 
SHA1 
SHA256 
SHA512 
Blowfish

For example, on AIX this is coded in the /etc/security/login.cfg file.

The error is received if you use the unsupported encryption algorithm and may show up in the db2diag log like this: 

2011-10-19-14.05.06.682321-300 I2777674A527 LEVEL: Severe
PID : 9633910 TID : 258 PROC : db2ckpwd 0
INSTANCE: db2inst1 NODE : 000
EDUID : 258 EDUNAME: db2sysc 0
FUNCTION: DB2 UDB, oper system services, sqloGetUserAttribByName, probe:50
MESSAGE : Unsupported password encryption algorithm in use! Configure the 
Operating System to use a supported algorithm.
DATA #1 : Hexdump, 4 bytes
0x07000000003F7F00 : 870F 00CD .... 

To check what encryption algorithm is being used the login.cfg file will contain something like:

usw:
shells = /bin/sh,/bin/bsh,/bin/csh,/bin/ksh,/bin/tsh,/bin/ksh93,/usr/bin
/sh,/usr/bin/bsh,/usr/bin/csh,/usr/bin/ksh,/usr/bin/tsh,/usr/bin/ksh93,/usr/sbin
/sliplogin,/usr/sbin/uucp/uucico,/usr/sbin/snappd,/bin/false
maxlogins = 32767
logintimeout = 60
pwd_algorithm = sha256
auth_type = STD_AUTH

OR the passwd.txt file will show something like this: 

root:
password = {sha256}06$SBysqAi4UQQ1nxC3$L55aKwmscvwxnKskkVrMk0HddbJyNkoE
v6HNXoLO.kH

ALL

Please use one of the supported encryption algorithms based on the version of DB2 that is being used.

When a DB2 Java application is running a dynamic SQL statement, it uses a dynamic section from DB2 CLI packages at DB2 server side to prepare/execute the statement. Please note that these DB2 CLI packages are exactly the same packages used by a CLI application.
By default, there are 3 large CLI packages (containing 385 sections) and 3 small CLI packages (containing 65 sections) bound at a DB2 database server. There are two sections from each package (both large and small) which are reserved for positioned update/delete statements and execute immediate statements. Therefore the total number of available sections for all other statements by default is (3 * 63) + (3 * 383) = 1338, which means by default a DB2 Java application can only run 1338 dynamic statements at one time.

When this limit is hit, the application will receive an error of "Out of Package Error Occurred" with SQLCODE -805, which means the DB2 server was running out of dynamic sections available from the CLI packages.

For example, running below Java application would hit the limit and report the error as below:


PreparedStatement pStmt = null;
for (int i=1; i<=1339; i++) {
pStmt = db.con.prepareStatement("insert into myt values (1, 'name1')");
pStmt.execute();
}


***** Out of Package Error Occurred (2014-05-14 22:20:32.431) *****

Exception stack trace:
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106
com.ibm.db2.jcc.am.bd.a(bd.java:682)
com.ibm.db2.jcc.am.bd.a(bd.java:60)
com.ibm.db2.jcc.am.bd.a(bd.java:127)
com.ibm.db2.jcc.am.io.c(io.java:2706)
com.ibm.db2.jcc.t4.ab.p(ab.java:872)
com.ibm.db2.jcc.t4.ab.h(ab.java:144)
com.ibm.db2.jcc.t4.ab.b(ab.java:41)
com.ibm.db2.jcc.t4.p.a(p.java:32)
com.ibm.db2.jcc.t4.qb.i(qb.java:135)
com.ibm.db2.jcc.am.io.gb(io.java:2112)
com.ibm.db2.jcc.am.jo.rc(jo.java:3526)
com.ibm.db2.jcc.am.jo.b(jo.java:3976)
com.ibm.db2.jcc.am.jo.hc(jo.java:2732)
com.ibm.db2.jcc.am.jo.execute(jo.java:2715)
DbConn2.main(DbConn2.java:74)

Concurrently open statements:
1. SQL string: INSERT INTO MYT VALUES (1, 'NAME1')
Number of statements: 1339
********************

DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106


Then what to do if you encounter above errors?

1) firstly you need to check if you can modify the application to avoid running so many dynamic SQL statements at the same time. 

2) If you can't avoid it, you will need to increase the number of available sections by increasing the number of DB2 CLI packages. 

Please note that only the number of DB2 CLI large packages can be increased, not for small packages, and the maximum number of large packages you can increase to is 30, which also basically means the maximum available sections would be (3 * 63) + (30 * 383) = 11679.

3) To increase the number of large CLI packages, you can run DB2 bind command with CLIPKG option via the instance owner as following example:

cd ~/sqllib/bnd
db2 connect to SAMPLE

--CLPKG 10, means it will increase the number of large CLI packages to 10.
db2 "bind @db2cli.lst CLIPKG 10 grant public blocking all"

--You can then check the number of CLI large packages per isolation and cursor holdability, where you will see the number would be 10 now.

db2 "list packages for all"

db2 terminate

In both techniques outlined below, the explain tables need to be created. For more information on doing this task, see Explain Tables.

Technique 1: Obtain the explain plan when creating the stored procedure:

1) Turn on explain either:

• Dynamically within the scope of the current session by issuing
  db2 "call SET_ROUTINE_OPTS('EXPLAIN ALL')" 
  
• Or, globally at the instance level if the procedure is not being called within the current session
  db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
  db2 terminate 
  db2stop 
  db2start 
  

• If it was turned on for the current session: 
  db2 "CALL SYSPROC.SET_ROUTINE_OPTS('')"
  
• If it was turned on globally
  db2set DB2_SQLROUTINE_PREPOPTS=
  db2 terminate 
  db2stop 
  db2start

db2 "SELECT executable_id FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL,NULL, -1)) AS T where stmt_text like '%INSERT INTO SYSIBM.SYSDUMMY1%'"

3) With the executable_id returned in step 2 (For example: x'0100000000000000581E00000000000002000000010020140109134503816499'), make the following call. Also replace EXPLAIN_SCHEMA with your actual explain schema.

db2 "call EXPLAIN_FROM_SECTION(x'0100000000000000581E00000000000002000000010020140109134503816499', 'M', NULL, 0, 'EXPLAIN_SCHEMA', ?, ?, ?, ?, ?)"

This will populate the explain tables under the schema you specified

4) Run this command to extract the explain plan:
db2exfmt -d <db name> -g TIC -w -1 -n % -s % -# 0 -o exfmt.out