php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #59495 SQLSTATE 560C4, SQLCODE -5028 on iseries
Submitted: 2010-11-05 13:55 UTC Modified: 2015-09-03 13:22 UTC
From: adkinsm at amerch dot com Assigned:
Status: Not a bug Package: ibm_db2 (PECL)
PHP Version: 5.2.11 OS: iseries
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: adkinsm at amerch dot com
New email:
PHP Version: OS:

 

 [2010-11-05 13:55 UTC] adkinsm at amerch dot com
Description:
------------
Running ZendCore 2.6.6 which our admin says is the latest release. ibm_db2 says 1.8.1 on phpinfo.  The code segment below is running on the iseries and connecting to DB2 9 running on AIX.  The error being returned is: SQLSTATE = 560C4 - COMMIT(*NONE) valid only if relational database OTPDEV (product identification SQL09013) is a System i. SQLCODE=-5028.  Even using "WITH CS" does not change the error...

Reproduce code:
---------------
  // Use credentials to connect to OTP Server
  $OTPDBConnection = db2_connect( $OTPServer, $OTPUser, $OTPPW, $OTPConOptions );
  if ( !$OTPDBConnection ) { throw new Exception ("Failed to connect to '$OTPServer' at '$OTPUrl'. SQLSTATE = " . db2_conn_error() . " - " . db2_conn_errormsg()); }
  
  // Connected so show the client and server info
  echo "\n\n<BR><BR>OTP:";
  db2_client_print( $OTPDBConnection );
  db2_server_print( $OTPDBConnection );
  echo "\n<BR>OTPConOptions: "; var_dump( $OTPConOptions );
  
  $sql = "SELECT * FROM SYSIBM.SYSTABLES WITH CS";
  $tablesStmt = db2_prepare( $OTPDBConnection, $sql );
  if ( !$tablesStmt ) { throw new Exception( "Failed to prepare '$sql'. SQLSTATE = " . db2_stmt_error() . " - " . db2_stmt_errormsg()); }

Expected result:
----------------
Same thing without the exception at the end.

Actual result:
--------------
OTP: 

db2_client_info 
DRIVER_NAME: string(13) "QSQCLI.SRVPGM" 
DRIVER_VER: string(5) "09013" 
DATA_SOURCE_NAME: string(6) "OTPDEV" 
DRIVER_ODBC_VER: string(4) "3.00" 
ODBC_VER: NULL 
ODBC_SQL_CONFORMANCE: string(4) "CORE" 
APPL_CODEPAGE: NULL 
CONN_CODEPAGE: NULL 

db2_server_info 
DBMS_NAME: string(9) "DB2/AIX64" 
DBMS_VER: string(5) "09013" 
DB_CODEPAGE: NULL 
DB_NAME: string(6) "OTPDEV" 
INST_NAME: NULL 
SPECIAL_CHARS: NULL 
KEYWORDS COUNT: int(179) 
KEYWORDS: string(1594) "AFTER, ALIAS, ALLOW, APPLICATION, ASSOCIATE, ASUTIME, AUDIT, AUX, AUXILIARY, BEFORE, BINARY, BUFFERPOOL, CACHE, CALL, CALLED, CAPTURE, CARDINALITY, CCSID, CLUSTER, COLLECTION, COLLID, COMMENT, CONCAT, CONDITION, CONTAINS, COUNT_BIG, CURRENT_LC_CTYPE, CURRENT_PATH, CURRENT_SERVER, CURRENT_TIMEZONE, CYCLE, DATA, DATABASE, DAYS, DB2GENERAL, DB2GENRL, DB2SQL, DBINFO, DEFAULTS, DEFINITION, DETERMINISTIC, DISALLOW, DO, DSNHATTR, DSSIZE, DYNAMIC, EACH, EDITPROC, ELSEIF, ENCODING, END-EXEC1, ERASE, EXCLUDING, EXIT, FENCED, FIELDPROC, FILE, FINAL, FREE, FUNCTION, GENERAL, GENERATED, GRAPHIC, HANDLER, HOLD, HOURS, IF, INCLUDING, INCREMENT, INHERIT, INOUT, INTEGRITY, ISOBID, ITERATE, JAR, JAVA, LABEL, LC_CTYPE, LEAVE, LINKTYPE, LOCALE, LOCATOR, LOCATORS, LOCK, LOCKMAX, LOCKSIZE, LONG, LOOP, MAXVALUE, MICROSECOND, MICROSECONDS, MINUTES, MINVALUE, MODE, MODIFIES, MONTHS, NEW, NEW_TABLE, NOCACHE, NOCYCLE, NODENAME, NODENUMBER, NOMAXVALUE, NOMINVALUE, NOORDER, NULLS, NUMPARTS, OBID, OLD, OLD_TABLE, OPTIMIZATION, OPTIMIZE, OUT, OVERRIDING, PACKAGE, PARAMETER, PART, PARTITION, PATH, PIECESIZE, PLAN, PRIQTY, PROGRAM, PSID, QUERYNO, READS, RECOVERY, REFERENCING, RELEASE, RENAME, REPEAT, RESET, RESIGNAL, RESTART, RESULT, RESULT_SET_LOCATOR, RETURN, RETURNS, ROUTINE, ROW, RRN, RUN, SAVEPOINT, SCRATCHPAD, SECONDS, SECQTY, SECURITY, SENSITIVE, SIGNAL, SIMPLE, SOURCE, SPECIFIC, SQLID, STANDARD, START, STATIC, STAY, STOGROUP, STORES, STYLE, SUBPAGES, SYNONYM, SYSFUN, SYSIBM, SYSPROC, SYSTEM, TABLESPACE, TRIGGER, TYPE, UNDO, UNTIL, VALIDPROC, VARIABLE, VARIANT, VCAT, VOLUMES, WHILE, WLM, YEARS" 
DFT_ISOLATION: string(2) "NC" 
ISOLATION_OPTION: string(9) "CS RR NC " 
SQL_CONFORMANCE: string(0) "" 
PROCEDURES: bool(true) 
IDENTIFIER_QUOTE_CHAR: string(1) """ 
LIKE_ESCAPE_CLAUSE: bool(true) 
MAX_COL_NAME_LEN: int(128) 
MAX_ROW_SIZE: int(2145648640) 
MAX_IDENTIFIER_LEN: NULL 
MAX_INDEX_SIZE: NULL 
MAX_PROC_NAME_LEN: NULL 
MAX_SCHEMA_NAME_LEN: int(10) 
MAX_STATEMENT_LEN: int(2097152) 
MAX_TABLE_NAME_LEN: int(18) 
NON_NULLABLE_COLUMNS: bool(true) 
OTPConOptions: array(2) { ["autocommit"]=> int(1) ["DB2_ATTR_CASE"]=> int(2) } 
Caught exception: Failed to prepare 'SELECT * FROM SYSIBM.SYSTABLES WITH CS'. SQLSTATE = 560C4 - COMMIT(*NONE) valid only if relational database OTPDEV (product identification SQL09013) is a System i. SQLCODE=-5028 


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-11-23 13:50 UTC] adc at us dot ibm dot com
DB2 LUW does not support running statements when the isolation level of the DRDA package is *NONE, which is what the error below indicates is the problem. 

Try a work around ...
$conn=db2_connect("myAIX","myUID","myPWD",array('i5_commit'->DB2_I5_TXN_READ_COMMITTED));

May also need php.ini setting ...
ibm_db2.i5_allow_commit=1
... switch on.

/* i5_commit - SQL_ATTR_COMMIT
   The SQL_ATTR_COMMIT attribute should be set before the SQLConnect(). If the value is changed after the connection has been established, and the connection is to a remote data source, the change does not take effect until the next successful SQLConnect() for the connection handle
   DB2_I5_TXN_NO_COMMIT - Commitment control is not used.
   DB2_I5_TXN_READ_UNCOMMITTED - Dirty reads, nonrepeatable reads, and phantoms are possible. 
   DB2_I5_TXN_READ_COMMITTED - Dirty reads are not possible. Nonrepeatable reads, and phantoms are possible. 
   DB2_I5_TXN_REPEATABLE_READ - Dirty reads and nonrepeatable reads are not possible. Phantoms are possible. 
   DB2_I5_TXN_SERIALIZABLE - Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible
*/

Theory is that script running on the IBM i, the 'i5_commit' keyword should trigger ibm_db2.c to call ... 
rc = SQLSetConnectAttr((SQLHDBC)((conn_handle*)handle)->hdbc, SQL_ATTR_COMMIT, (SQLPOINTER)&pvParam, SQL_NTS);
... where hdbc is AIX DB2 9 handle (LUW).
 [2015-09-03 13:22 UTC] rangercairns@php.net
-Status: Open +Status: Not a bug
 [2015-09-03 13:22 UTC] rangercairns@php.net
Not a bug
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Dec 11 00:01:28 2024 UTC