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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
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: Sat Dec 21 18:01:29 2024 UTC