Development: Issues related to table creation in DB2 due to abnormal collection

Development: Issues related to table creation in DB2 due to abnormal collection

Because the table creation statements may be different when using the DB2 database for the first time, the Manual table creation method is used to fill in fields one by one. Check the primary key and the Creation fails. Read the SQL statements generated by the system.

The SQL statement is as follows:

Create table front. SCORE_REVERSE
(
SERIAL_NO VARCHAR (50 ),
SECRET_KEY VARCHAR (50 ),
TRADEBOX_ID INTEGER (32 ),
REDUCE_POINT_TYPE VARCHAR (50 ),
Performance_point_reason VARCHAR (50 ),
Performance_time VARCHAR (50 ),
LOGIN_NAME VARCHAR (50 ),
COMMODITY_ID INTEGER (32 ),
COMMODITY_NUMBER VARCHAR (50 ),
Point integer (32 ),
CONTACT_NAME VARCHAR (50 ),
CONTACT_MOBILE_NO VARCHAR (50 ),
CONTACT_ADDRESS VARCHAR (50 ),
COMMODITY_TYPE VARCHAR (50 ),
REVERSE_TIME VARCHAR (50 ),
Primary key (SERIAL_NO ));

Two errors are prompted:

First exception: [CREATE-0 row (s), 0.000 secs] [Error Code:-104, SQL State: 42601] DB2 SQL Error: SQLCODE =-104, SQLSTATE = 42601, SQLERRMC = INTEGER; R (50 ),

Because I couldn't find the desired answer on the internet, I directly looked at the problem and found that the prompt SQLERRMC = INTEGER; R (50) is removed from the INTEGER length, and I changed INTEGER (32) to INTEGER. Problem Solving

Second exception: DB2 SQL Error: SQLCODE =-542, SQLSTATE = 42831, SQLERRMC = SERIAL_NO, DRIVER = 4.15.82

This makes me unable to find my mind. After searching, I found that when DB2 creates a primary key, its primary key SQL must be set not to null. That is to say, set it to: SERIAL_NO VARCHAR (50) not null, and the system generates no not null

Modify the SQL statement again and run OK again. The table is created.

Db2 table creation statement

-- Take table 1 as an example to create a table named table2 with the same structure
Create global temporary table table2 LIKE table1;

-- Insert the data in table 1 to table 2.
Insert into table2 select * from table1;

How to query the SQL statements for table creation in DB2

Select name from sysibm. Tables Ables where type = 'T' and creator = 'db2admin'
Type: the database table is represented by T, and the database view is represented by V.
Creator: creator of the database object
Select tabname from syscat. tables where tabschema = current schema; // obtain all user tables in the current Mode

To add this question, use show tables in mysql to query the table.
Hope to adopt ~