Error attempting to CREATE a DBMS table.

[thiagolemosp1]

Hello everyone! I've spent the whole morning trying to figure this out, so im asking you for help: 

The following code works and creates both tables WORK.VENDAS_INDV_MES and DB2VNOT.VENDAS_INDV_MES: 

PROC SQL;
   CREATE TABLE WORK.VENDAS_INDV_MES AS 
   SELECT DISTINCT t1.AAAAMM, 
          t1.NR_UNCO_CTR_OPR, 
          t1.NR_CTR_OPR, 
          t1.CD_CLI, 
          t1.CD_CPNT_RSTD, 
          t1.CD_PRF_RSTD, 
          t1.DT_APRC, 
          t1.MES, 
          t1.ANO, 
          t1.CD_DIRETORIA, 
          t1.NM_DIRETORIA, 
          t1.SUPER, 
          t1.NM_SUPER, 
          t1.CD_GEREV,
          t1.NM_GEREV, 
          t1.PREFIXO_FUNCI, 
          t1.NM_PREFIXO_FUNCI, 
          t1.CD_PRF_CTRA_CLI, 
          t1.CD_TIP_CTRA_CLI, 
          t1.MAT_FUNCI, 
          t1.NM_FUNCI, 
          t1.FUNCAO,
	  t1.SEXO, 
          t1.CD_ITCE_CNL_ATDT, 
          t1.CD_PRD, 
          t1.CD_MDLD, 
          t1.NM_MDLD,
          t1.VL_CPNT_RSTD, 
          t1.VL_ULT_SDO, 
          t1.COMPONENTE
      FROM WORK.INCLUI_NM_MDLD t1
	       WHERE t1.AAAAMM = &AnoMes;
QUIT;

proc sql; 
drop table DB2VNOT.VENDAS_INDV_MES; 
quit;

PROC SQL;
   CREATE TABLE DB2VNOT.VENDAS_INDV_MES AS 
   SELECT DISTINCT /*t1.AAAAMM,*/
          t1.NR_UNCO_CTR_OPR,
          /*t1.NR_CTR_OPR,*/ 
          /*t1.CD_CLI,*/
          t1.CD_CPNT_RSTD,
        /*t1.CD_PRF_RSTD,*/
          t1.DT_APRC, 
          t1.MES, 
          t1.ANO, 
          t1.CD_DIRETORIA, 
          t1.NM_DIRETORIA, 
	  t1.SUPER, 
          t1.NM_SUPER, 
          t1.CD_GEREV,
          t1.NM_GEREV, 
          t1.PREFIXO_FUNCI, 
          t1.NM_PREFIXO_FUNCI,
        /*t1.CD_PRF_CTRA_CLI,*/ 
          t1.CD_TIP_CTRA_CLI,
          t1.NM_FUNCI, 
          t1.FUNCAO,
	  t1.SEXO, 
          t1.CD_ITCE_CNL_ATDT,
          t1.NM_MDLD,
          t1.VL_CPNT_RSTD,
          t1.VL_ULT_SDO,
          t1.COMPONENTE
      FROM WORK.VENDAS_INDV_MES t1;
QUIT;

But, if i uncomment any of the commented columns above i get this error on DB2VNOT.VENDAS_INDV_MES :

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:  ", ) UNIQUE REFERENCES PRIMARY CONSTRAINT INLINE IMPLICITLY".  
       SQLSTATE=42601 .

I've also tried:

data DB2VNOT.VENDAS_INDV_MES;
set WORK.VENDAS_INDV_MES;
run;

An got the same error. 

None of those columns is suposed to have a UNIQUE Constraint

[SASKiwi]

SAS Notes include a few similar DB2 errors relating to unexpected tokens, like this one: https://support.sas.com/kb/50/686.html

The suggestion is that the underlying DDL is not well-formed. Adding this SAS option might provide more evidence: options sastrace = ',,,d' SASTRACELOC = SASLOG;

[ballardw]

I am wondering if the  "drop table" you attempt is actually removing metadata like constraints.

I also would not be surprised to see table created with Select Distinct to end up with a unique constraint as that is the effect of the Distinct. You don't mention which  any of the connection details you use that might influence such a creation.

Do you get the same error if you create a brand new never been used table name in DB2VNOT?

[thiagolemosp1]

I thought about this too and tested it by removing the DISTINCT. The result was the same. Sorry for not mentioning this on the original post

[Tom]

How are the variables you commented out different than the one you included?

Different TYPE? Different LENGTH?  Do they have different FORMATs attached to them?

[thiagolemosp1]

The problematic variables are all numeric and have format and informat 11. or 18. :
Formats

For testing, i changed the code to remove all format:

data WORK.VENDAS_INDV_MES;
set WORK.VENDAS_INDV_MES;
FORMAT _all_;
INFORMAT _all_;
run;

proc sql; 
drop table DB2VNOT.VENDAS_INDV_MES; 
quit;

PROC SQL;
   CREATE TABLE DB2VNOT.VENDAS_INDV_MES AS 
   SELECT DISTINCT t1.AAAAMM,
          t1.NR_UNCO_CTR_OPR,
          t1.NR_CTR_OPR,
          t1.CD_CLI,
          t1.CD_CPNT_RSTD,
   		  t1.CD_PRF_RSTD,
          t1.DT_APRC, 
          t1.MES, 
          t1.ANO, 
          t1.CD_DIRETORIA, 
          t1.NM_DIRETORIA, 
	      t1.SUPER, 
          t1.NM_SUPER, 
          t1.CD_GEREV,
          t1.NM_GEREV, 
          t1.PREFIXO_FUNCI, 
          t1.NM_PREFIXO_FUNCI,
		  t1.CD_PRF_CTRA_CLI, 
          t1.CD_TIP_CTRA_CLI,
          t1.NM_FUNCI, 
          t1.FUNCAO,
	      t1.sexo, 
          t1.CD_ITCE_CNL_ATDT,
   		  t1.NM_MDLD,
		  t1.VL_CPNT_RSTD,
          t1.VL_ULT_SDO,
          t1.COMPONENTE
      FROM WORK.VENDAS_INDV_MES t1;
QUIT;.

And it sucessfully created the table on DB2VNOT.  

Is there anything I can do to avoid being forced to change formats?

[Patrick]

This starts to feel like something you should raise with SAS Tech Support. 

[Tom]

SAS will normally use the format attached to a variable to guess what type to use when making a variable in a remote databases.  Something with format like 11. that displays only integers might be converted to a DECIMAL() type.

You might check if any of the values of those variables are not actually integers.  Say 10.25.  Such a value could not be storing into a variable that only accepts integers.

Note that you can use the DBTYPE= dataset option to control the type that SAS uses to define the variable in the remote database.

[Patrick · Accepted solution]

@Tom got an interesting idea with format 11. and integers but I can't see this really supported by the documentation LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats

I would expect for any numerical SAS variable that hasn't a date, time or datetime format attached to convert to Decimal independent of the format. 

Now... based on the error it could be that the conversion to DB2 type DECIMAL(m,n) gets wrongly created because the d component in the SAS format is "missing".

I still feel there is either something not as it should with the access engine or then you're not using a supported driver. That's something SAS Tech Support should be able to support you with.

As you already mentioned a workaround could be to just remove w.d formats. Below some sample code how you could do this programmatically.

data work.vendas_indv_mes;
  format num_var1 date9. num_var2 11.;
  num_var1=date();
  num_var2=45.2;
run;

%let varlist=;
proc sql noprint;
  select name into :varlist separated by ' '
  from dictionary.columns
  where 
    libname='WORK' 
    and memname='VENDAS_INDV_MES' 
    and type='num'
    and anydigit(format)=1
  ;
quit;

data work.v_vendas_indv_mes/view=work.v_vendas_indv_mes;
  set work.vendas_indv_mes;
  format &varlist;
run;

/** and here the SQL that loads the table into DB2     **/
/** - to just load a whole table Proc Append is faster **/ 

proc contents data=work.v_vendas_indv_mes;
run;quit;

proc print data=work.v_vendas_indv_mes;
run;

[thiagolemosp1]

Thank you so much for taking the time to help me. Im new into programing and i hope to learn quicly to start helping like you did.