Issue with loading data from SAS into postgres database (SAS data integration studio)

[suryat]

We are trying to load data from SAS to postgres database by running jobs in SAS Data Integration Studio (4.9).

We are getting the following error at the table loader.

ERROR: CLI execute error: ERROR: current transaction is aborted, commands ignored until end of transaction block; Error while preparing parameters

Tables are being created in the Database but no data is being inserted into the tables.

Can you please guide us on this.

Appreciate your help on this.

[LinusH]

What libname options have you used?

If you set 

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

you'll may get more informaiton in the log, perhqaps at least what SQl SAS is pushing down.

A quick Google geave me hint on that this might be work aroundable bgy using autocommit.

https://stackoverflow.com/questions/10399727/psqlexception-current-transaction-is-aborted-commands-i...

[suryat]

Hi,

We have tried shared option but we are not able to trace out the exact error.

I am sharing my both environment (UAT & Production) logs, if you able to explain or identify issue then pls help us.

for production error:

POSTGRES_4: Prepared: on connection 1
SELECT * FROM "datamart".DM_GSTR9_TBL_TAX_PAID WHERE 0=1

POSTGRES: AUTOCOMMIT is NO for connection 2
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

POSTGRES_5: Executed: on connection 2
CREATE TABLE "datamart".DM_GSTR9_TBL_TAX_PAID ("called_dt" VARCHAR(10),"fil_dt" VARCHAR(10),"gstin" VARCHAR(15),"fp"
VARCHAR(6),"isnil" VARCHAR(1),"txpaid_igst" DOUBLE PRECISION,"txpyble_igst" DOUBLE PRECISION,"txpaid_cgst" DOUBLE
PRECISION,"txpyble_cgst" DOUBLE PRECISION,"txpaid_sgst" DOUBLE PRECISION,"txpyble_sgst" DOUBLE PRECISION,"txpaid_cess" DOUBLE
PRECISION,"txpyble_cess" DOUBLE PRECISION,"txpaid_intr" DOUBLE PRECISION,"txpyble_intr" DOUBLE PRECISION)

POSTGRES: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
POSTGRES: COMMIT performed on connection 2.
POSTGRES: COMMIT performed on connection 2.
NOTE: The data set DWH.DM_GSTR9_TBL_TAX_PAID has 0 observations and 15 variables.
POSTGRES: COMMIT performed on connection 2.
POSTGRES: COMMIT performed on connection 2.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2020-12-19T13:52:20,547+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 132886528| _DISARM| 20090880| _DISARM| 11| _DISARM| 24| _DISARM| 0| _DISARM| 809888| _DISARM| 0.010000| _DISARM|
0.055709| _DISARM| 1923985340.491702| _DISARM| 1923985340.547411| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.02 seconds

MPRINT(ETLS_LOADER): ;
NOTE: Appending data ...

POSTGRES_6: Prepared: on connection 1
90 The SAS System Saturday 19 December 2020 01:16:00 PM

SELECT * FROM "datamart".DM_GSTR9_TBL_TAX_PAID WHERE 0=1

POSTGRES: AUTOCOMMIT is NO for connection 2
POSTGRES: COMMIT performed on connection 2.

POSTGRES_7: Prepared: on connection 2
SELECT * FROM "datamart".DM_GSTR9_TBL_TAX_PAID

MPRINT(ETLS_LOADER): proc append base = DWH.dm_gstr9_tbl_tax_paid data = WORK.W7K8BK8S () force ;
MPRINT(ETLS_LOADER): run;

NOTE: Appending WORK.W7K8BK8S to DWH.DM_GSTR9_TBL_TAX_PAID.
NOTE: SAS threaded sort was used.

POSTGRES_8: Prepared: on connection 2
INSERT INTO "datamart".DM_GSTR9_TBL_TAX_PAID
("called_dt","fil_dt","gstin","fp","isnil","txpaid_igst","txpyble_igst","txpaid_cgst","txpyble_cgst","txpaid_sgst","txpyble_sgst","t
xpaid_cess","txpyble_cess","txpaid_intr","txpyble_intr") VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )


POSTGRES_9: Executed: on connection 2
Prepared statement POSTGRES_8

POSTGRES: ROLLBACK performed on connection 2.
NOTE: There were 20000 observations read from the data set WORK.W7K8BK8S.
NOTE: View WORK.W7JXE9GV.VIEW used (Total process time):
real time 1.62 seconds
cpu time 2.52 seconds

NOTE: There were 185332 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_CAMT.
NOTE: There were 185332 observations read from the data set WORK.W3Z7RQCW.
NOTE: There were 184909 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_CSAMT.
NOTE: There were 184909 observations read from the data set WORK.W3Z7RQCZ.
NOTE: There were 185331 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_SAMT.
NOTE: There were 185331 observations read from the data set WORK.W3Z7RQD1.
NOTE: There were 185164 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_IAMT.
NOTE: There were 185164 observations read from the data set WORK.W3Z7RQD2.
NOTE: There were 183464 observations read from the data set S_GSTR9.STG_GSTR9_TBL_TABLE14_INTR.
NOTE: There were 183464 observations read from the data set WORK.W3Z7RQD4.
NOTE: 19999 observations added.
NOTE: The data set DWH.DM_GSTR9_TBL_TAX_PAID has . observations and 15 variables.
POSTGRES: COMMIT performed on connection 2.
POSTGRES: COMMIT performed on connection 2.
ERROR: CLI execute error: ERROR: current transaction is aborted, commands ignored until end of transaction block;
preparing parameters
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 1.73 seconds
cpu time 2.55 seconds

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2020-12-19T13:52:22,288+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 132886528| _DISARM| 20877312| _DISARM| 11| _DISARM| 24| _DISARM| 268816| _DISARM| 1078704| _DISARM| 2.550000|
_DISARM| 1.739518| _DISARM| 1923985340.549101| _DISARM| 1923985342.288619| _DISARM| 2.070000| _DISARM| | _ENDDISARM
MPRINT(ETLS_LOADER): ;