Create nickname to DB2/i table with number(38,0) column fails with SQL0901N error

DATASD3 is cataloged DB2/i (DB2 AS400) database.

db2 connect to DATASD3 user user1 using PASSWORD
Database Connection Information
Database server = OS/400 5.4.0
SQL authorization ID = USER1
Local database alias = DATASD3

db2 "create table tn(c1 NUMERIC(38 , 0))"

DB20000I The SQL command completed successfully.

db2 "select * from tn "
SQL30020N Execution of the command or SQL statement failed because of a
syntax error in the communication data stream that will affect the successful
execution of subsequent commands and SQL statements: Reason Code
"0x220A"("0100")"". SQLSTATE=58009

FEDDB is Federated database
db2 connect to FEDDB
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "Invalid nickname
column length".) SQLSTATE=58004


The maximum precision/scale the DB2/LUW supports is 31. You can NOT query numeric type with precision > 31. This is documented here:
A decimal value is a packed decimal number with an implicit decimal point. The position of the decimal point is determined by the precision and the scale of the number. The scale, which is the number of digits in the fractional part of the number, cannot be negative or greater than the precision. The maximum precision is 31 digits.

Workaround on Federation

On DB2/i Server 5.4 or later:
db2 "create view v_tn as select cast(c1 as double) as c1 from tn"
On Federation Server

db2 "create nickname n1 for SD3SERVER.user1.v_tn"
DB20000I The SQL command completed successfully.

On DB2/i Server earlier than 5.4 version, you need to cast the number(38,0) to number(31,0) if the data stored in the table doesn't have data with precision >31.

Views: 946


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service