Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Inserting values from IBM DB2 into Teradata

Kavya432
8 - Asteroid

Hi Team,

 

 

I am trying to insert values from IBM DB2 table into teradata database table.Please find the attached data flow of mine. I am extracting the data successfully but while inserting into Teradata its causing an issue.

 

I tried post sql and all different methods but didn't had any luck. Appreciate you response.

 

clipboard_image_1.png

clipboard_image_0.png

Below is the table syntax I used to create table in Teradata, its been successfully created but couldn't able to insert the values into the table through Alteryx.

 CREATE MULTISET TABLE CA_Business_Info_Team.NWACD_AMF ,NO FALLBACK ,
      NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      Record INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 2147483647 
            NO CYCLE),

      RES_ST_# VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      RES_ST VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      RES VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      NPN VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      LAST_NAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      FIST_NAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      AGENT_DEFf VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      PDCR_TYPE VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      APPT_STATUS VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      DISB VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      LIC_NUM VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      EMAIL VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      AGENCY_NAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      AGENCY_TID VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      REGION VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      STATE VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      MKTG_TERR VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( NPN );




 

4 REPLIES 4
jrgo
14 - Magnetar

Hi @Kavya432 

I noticed in your Output tool that you have the option "Take File/Table Nam

e From Field" enabled. As it's configured it's modifying the target output table that's configured in the tool.

 

To elaborate further, the output tool is targeting the table "NWACD_AMF". But with the configuration, at runtime it'll actually target

"NWACD_AMF<value for [AGENCY_NAME]>"

image.png

 

If this was intentional, you may want to verify that the table for each "AGENCY_NAME" exists. You could also disable this option just to confirm whether it still generates an error or not to rule this issue out. 

 

Also, I recall that Alteryx did have issues inserting records into tables that contained an auto incrementing identify column, but this was for MS SQL Server. I think it was addressed, but could be something with TD that wasnt fixed... this is completely speculatory though.

 

Hope this helps!

Jimmy
Teknion Data Solutions

Kavya432
8 - Asteroid

Thanks for your response Jimmy,

 

The option was unintentional and I unchecked the box you mentioned below, but it still showing an error message. Is there anything I am missing in Teradata connection setup. Not sure.

 

clipboard_image_1.png

below is how I configured Teradata as output file.

 

clipboard_image_2.png

 

 

I tried to add post sql as below:

 

INSERT INTO CA_Business_Info_Team.NWACD_AMF
(
RES_ST_# ,
RES_ST ,
RES ,
NPN ,
LAST_NAME ,
FIST_NAME ,
AGENT_DEFf ,
PDCR_TYPE ,
APPT_STATUS ,
DISB ,
LIC_NUM ,
EMAIL ,
AGENCY_NAME ,
AGENCY_TID ,
REGION ,
STATE ,
MKTG_TERR
)

VALUES
( ?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?

)

 

Its still shoes the error message thought he values are 17 from input file and I am creating the same.

 

clipboard_image_3.png

 

clipboard_image_4.png

 

Appreciate your Response!

 

Thanks

Kavya

jrgo
14 - Magnetar

@Kavya432 

 

Looking at the first error message regarding the object not existing, I'm thinking that your ODBC DSN may not have a default database defined. Its not required that a default DB be defined, but when there's not, you have to be more verbose to where your target table can be found. It needs to be entered as <database>.<table> syntax.

 

Assuming that:

DB = CA_Business_Info_Team

Table = NWACD_AMF

 

... update your output tool table reference to CA_Business_Info_Team.NWACD_AMF

image.png

 

Using a PostSQL is not going to work for this purpose.

 

Hope this helps!

Jimmy
Teknion Data Solutions

Kavya432
8 - Asteroid

Thanks A lot Jimmy. It worked after that change. Appreciate your help!

 

Thanks

Kavya

Labels