Create nickname to DB2/i table with number(38,0) column fails with SQL0901N error
Problem
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
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
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
db2 "CREATE NICKNAME n1 FOR SD3SERVER.user1.tn
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned:
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
Explanation
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.
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.
Comment
Featured Downloads
Explore in-memory and columnar technologies through this active approach to exploring emerging technologies.
 |
FREE Book: Getting started with DB2 |
© 2013 Created by channeldb2.
You need to be a member of ChannelDB2 to add comments!
Join ChannelDB2