SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating DB2 table

Reply
Occasional Contributor
Posts: 5

Creating DB2 table

[ Edited ]
 I'm trying to create a table in DB2, but the variable has 14 positions.
With variable with size below 9 I have no problems.
But when caught variables above that length I get the following error:
 
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "("
       was found following "".  Expected tokens may include:  "FIELDPROC CHECK GENERATED IMPLICITLY INLINE CONSTRAINT PRIMARY".  
       SQLSTATE=42601 .
 
 
Code:
 
proc sql;
    create table db2lib.test2 as
    select
     var1
 from
        work.test ;
quit;
 
 
max size of var1 is 99999999911119.
Super User
Posts: 6,938

Re: Crating DB2 table

Did you try

data db2lib.test2;
set work.test;
keep var1;
run;

?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Crating DB2 table

Same error

Super User
Posts: 5,257

Re: Crating DB2 table

Is it numerical or char?

If numerical, you may hit the default max size for the mapped data type in DB2.

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;
Data never sleeps
Super User
Posts: 6,938

Re: Crating DB2 table

It looks like the column is (per default) defined as integer in DB/2, where you have a range of -2147483648 to +2147483647.

 

One thing that came to my mind was to use PROC DS2 and define the column as BIGINT or FLOAT.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,257

Re: Crating DB2 table

When numerical variable become this large, on can ask if they really need to be integers - are they par of calculations?

Another option is to use data set options to explicitly specify data types in the target database (DBTYPE).

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 590 views
  • 1 like
  • 3 in conversation