Tag Archives: db2

DB2 detects a syntax error in the DRDA data stream: 0x3 ERRORCODE= -4499, SQLSTATE=58009

DB2 reports an error and detects a syntax error in the DRDA data stream Reason: 0x3 ERRORCODE= -4499, SQLSTATE=58009

[16:48:43] RMI TCP Connection(3)-127.0.0.1 ERROR  [] [] [com.alibaba.druid.pool.DruidDataSource] - 
dataSource init errorcom.ibm.db2.jcc.am.DisconnectNonTransientException: 
 [jcc][4][2034]11148][4.26.14] conversation was released due to a distribution protocol error.
thus causing the execution to fail. Cause: 0x3. ERRORCODE= -4499, SQLSTATE=58009
	at com.ibm.db2.jcc.am.b6.a(b6.java:340)
	at com.ibm.db2.jcc.am.b6.a(b6.java:463)
	at com.ibm.db2.jcc.t4.y.j(y.java:1016)
	at com.ibm.db2.jcc.t4.y.c(y.java:472)
	at com.ibm.db2.jcc.t4.y.v(y.java:1219)
	at com.ibm.db2.jcc.t4.z.a(z.java:53)
	at com.ibm.db2.jcc.t4.b.c(b.java:1410)
	at com.ibm.db2.jcc.t4.b.b(b.java:1282)
	at com.ibm.db2.jcc.t4.b.b(b.java:833)
	at com.ibm.db2.jcc.t4.b.a(b.java:804)
	at com.ibm.db2.jcc.t4.b.a(b.java:441)
	at com.ibm.db2.jcc.t4.b.a(b.java:414)
	at com.ibm.db2.jcc.t4.b.<init>(b.java:352)
	at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:233)
	at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:200)
	at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:471)
	at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:113)

Solution:

url ip can not use 127.0.0.1 and localhost; jdbc.url=jdbc:db2://localhost:50000/test ip can be changed to the real ip jdbc.url=jdbc:db2://192.168.xxx.xxx:50000/test

DB2 table annotation pull

Application scenarios:
company Intranet data tables built after migrating to customers online, usually cannot be attached to export comments, need further pull
table field notes:

SELECT
	*
FROM
	(
		SELECT
			'comment on column ' || trim(t.TABSCHEMA) || '.' || t.TABNAME || '.' || t.COLNAME || ' is ' || '''' || t.REMARKS || '''' || ' GO' AS COMMENT
		FROM
			syscat. COLUMNS t
	)
WHERE
	COMMENT IS NOT NULL

Table annotation:

SELECT
	*
FROM
	(
		SELECT
			'comment on table ' || trim(t.tabschema) || '.' || t.tabname || ' is ' || '''' || t.REMARKS || '''' || ' GO' AS COMMENT
		FROM
			syscat. TABLES t
	)
WHERE
	COMMENT IS NOT NULL

DB2 SQL ERROR: SQLCODE=-803, SQLSTATE=23505, SQL

As soon as I came into contact with DB2, I encountered a problem. I felt that the explanation of DB2 error was not obvious. The novice was overwhelmed by the error information as follows

Error for batch element #1: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQL

The reason is a violation of the uniqueness constraint…

DB2, create stored procedure error, sqlcode = – 104, sqlstate = 42601, PSM_ semicolon

Database editing software Aqua Data Studio/DbVisualizer
stored procedures as follows:

create or replace procedure DM.proc_test(
	in i_seq integer,
	out o_flag integer 
)
begin
  declare v_test varchar(20);
end;

[CREATE]DB2 SQL Error:SQLCODE=-104,SQLSTATE=42601,SQLERRMC= end-of-statement; E v_test varchar(20); < psm_semicolon>
[END]DB2 SQL Error:SQLCODE=-104,SQLSTATE=42601,SQLERRMC=END-OF-STATEMENT; END; JOIN < joined_table>

[CREATE]DB2 SQL Error:SQLCODE=-104,SQLSTATE=42601,SQLERRMC=END-OF-STATEMENT;E v_test varchar(20);<psm_semicolon>
[END]DB2 SQL Error:SQLCODE=-104,SQLSTATE=42601,SQLERRMC=END-OF-STATEMENT;END;JOIN <joined_table>

for database editing software or command Windows, the default closing symbol is “;” , so when the stored procedure above is executed, the statement will end only before the end statement.

modify the editor’s statement terminator to be other symbols
1) command window modify statement terminator (example, change to @) :
delimiter @
2) database software, modify the delimiter (delimiter) to be other in configuration

DB2 sql5005c system exception

Backup database error, SQL5005C, online to find the solution
The error is as follows: A system error,probably an I/O error,was ritten while soil A Configuration file
According to the online method, look for a file called db2systm. It doesn’t exist. Maybe it was deleted by mistake when cleaning the C disk.
IBM KNOWLEDGE CENTER website:
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql05005c.html

DB2 SQL error: sqlcode = – 803, sqlstate = 23505, sqlerrmc = 2 [solution]

DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2.
1, check under, probably means to violate the uniqueness constraints ah!
2. However, I found in DB2 DESCRIBE table XX that only ID of the table cannot be empty.
3, finally, directly wrote a SQL execution on the command line, the same error and code
Finally, I looked up the index in the following table and realized that a composite index had been created.
It turns out that the DBA created a unique constraint and index yesterday!
Although only ID cannot be null in a describe, the addition of a unique constraint and index results in the inability to insert data.
 

Connection authorization failure occurred. Reason: local security service non retryable error solution

Above the exception is when connecting to my, very strange, because DB2 USES for such a long time have never seen this problem, the single from the perspective of the exception message is obviously validation fails, the DB2 connect to a user name and password problem, the problem is why such a problem, when the user name and password are correct.

Study found that after is the system differences, the password can’t be in clear text mode is stored in the system, are saved after through the corresponding encryption algorithm, DB2 USES a SHA256 encryption, and a lot of high version of the Linux the default encryption algorithm is the SHA512, such system to create DB2 user password by SHA512 stored in the system, the connection to DB2 by SHA256 password passwords in encrypted and stored in the system, the result is, of course, failure, the solution is as follows:

Open the /etc/pam.d/system-auth file, find the second line of passwd configuration, change sha512 to Sha256, and save;

Rerun the passwd DB2 user and set the password;

Success;

This will replace the system encryption method with SHA256 and then update the saved password.