Bug #40969 Can't create table with 'ibmdb2i', shows SQL0901 error in the joblog
Submitted: 24 Nov 2008 9:14 Modified: 1 Apr 2009 9:33
Reporter: Nidhi Shrotriya Email Updates:
Status: Closed Impact on me:
None 
Category: Severity:S3 (Non-critical)
Version:5.1.29rc OS:Other (IBM i5os)
Assigned to: Nidhi Shrotriya CPU Architecture:Any

[24 Nov 2008 9:14] Nidhi Shrotriya
Description:
This issue is reported as part of certification testing of 'IBMDB2I' SE.

SQL statements fail to interact with DB2, showing SQL0901 error in the joblog.

mysql> use nidhi1;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table new1(i int);
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+------------------+
| Tables_in_nidhi1 |
+------------------+
| new1             |
+------------------+
1 row in set (0.00 sec)

mysql> create table new2(i int) engine=ibmdb2i;
ERROR 1005 (HY000): Can't create table 'nidhi1.new2' (errno: 2021)
mysql> show tables;
+------------------+
| Tables_in_nidhi1 |
+------------------+
| new1             |
+------------------+
1 row in set (0.00 sec)

From MySQL server log:
ibmdb2i error 2021: See message SQL0901 in joblog for job 516095/QUSER/QSQSRVR.

From debugging joblog:
-----------------------

                Display Job Log                                  
                                                             System:   S106E0CF 
 Job . . :   QSQSRVR       User . . :   QUSER         Number . . . :   516095   
                                                                                
     ACGDTA for 516095/QUSER/QSQSRVR not journaled; reason 1.                   
     Printer device PRT01 not found.                                            
     Errors on CHGJOB command for job 516095/QUSER/QSQSRVR.                     
  

                       Additional Message Information                         
                                                                                
 Message ID . . . . . . :   CPF1301       Severity . . . . . . . :   30         
 Message type . . . . . :   Information                                         
 Date sent  . . . . . . :   11/24/08      Time sent  . . . . . . :   05:51:05   
                                                                                
 Message . . . . :   ACGDTA for 516095/QUSER/QSQSRVR not journaled; reason 1.   
 Cause . . . . . :   Job resource accounting data for job 516095/QUSER/QSQSRVR  
   was not journaled to the system accounting journal QSYS/QACGJRN.             
     -- Reason codes and their meanings follow:                                 
     -- 1-The accounting level system value (QACGLVL) indicated that this level 
   of resource accounting was not to be done when the job entered the system.   
     -- 2-The accounting journal QSYS/QACGJRN is unable to receive the data.    
   The accounting data was sent to the history log (QHST) as the text of a      
   CPF1303 message.  See CPF1302 in the history log (QHST) for recovery action. 
     -- 3-The accounting journal QSYS/QACGJRN  was allocated to another job.    
   The accounting data was sent to the history log (QHST) as the text of a      
   CPF1303 message.                                                                                                 
                
                         Additional Message Information                         
                                                                                
 Message ID . . . . . . :   CPD0912       Severity . . . . . . . :   20         
 Message type . . . . . :   Diagnostic                                          
 Date sent  . . . . . . :   11/24/08      Time sent  . . . . . . :   05:51:05   
                                                                                
 Message . . . . :   Printer device PRT01 not found.                            
 Cause . . . . . :   The printer device was not found.                          
 Recovery  . . . :   Either create the printer device (CRTDEVPRT command) or if 
   the printer device name is not correct, change the printer device name and   
   then try the command again.                                                  
                                                                                
                                                                                
  
                         Additional Message Information                         
                                                                                
 Message ID . . . . . . :   CPF1336       Severity . . . . . . . :   40         
 Message type . . . . . :   Escape                                              
 Date sent  . . . . . . :   11/24/08      Time sent  . . . . . . :   05:51:05   
                                                                                
 Message . . . . :   Errors on CHGJOB command for job 516095/QUSER/QSQSRVR.     
 Recovery  . . . :   See the messages listed previously, if any. Correct the    
   errors and then submit the command again.                                    

How to repeat:
As mentioned in the description.
[24 Nov 2008 12:15] Nidhi Shrotriya
Also not able to drop any existing schema from db2 using mysql.

mysql> drop schema test;
Query OK, 0 rows affected (0.24 sec)
mysql> drop schema test;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist

Still exists in DB2->

bash-2.05b$ ls \"test\".LIB
"t1".FILE          QSQJRN0001.JRNRCV  SYSCST.FILE        SYSINDEXES.FILE    SYSPACKAGE.FILE    SYSTABLES.FILE     SYSTRIGGER.FILE    SYSVIEWS.FILE
"t2".FILE          SYSCHKCST.FILE     SYSCSTCOL.FILE     SYSKEYCST.FILE     SYSREFCST.FILE     SYSTRIGCOL.FILE    SYSTRIGUPD.FILE
QSQJRN.JRN         SYSCOLUMNS.FILE    SYSCSTDEP.FILE     SYSKEYS.FILE       SYSTABDEP.FILE     SYSTRIGDEP.FILE    SYSVIEWDEP.FILE
[1 Apr 2009 9:27] Nidhi Shrotriya
Issue seemed to get fixed after binary was built with the correct header file QMYSE.H