topic Re: SAS DI Table loader oracle in SAS Data Management

SAS DI Table loader oracle

Kashishjoker — Wed, 17 Mar 2021 13:57:39 GMT

Hello All-
 
I'm getting an below error while loading the data into oracle table with the help of SAS DI table loader
 
ODBC_168: Executed: on connection 6
Prepared statement ODBC_124

ODBC: ROLLBACK performed on connection 6.
NOTE: There were 1441748 observations read from the data set WORK.W8BZ164Z.
NOTE: 1441747 observations added.
NOTE: The data set table has . observations and 40 variables.
ODBC: COMMIT performed on connection 6.

Summary Statistics for ODBC are:
Total SQL execution seconds were: 978.921050
Total SQL prepare seconds were: 0.002722
Total SQL describe seconds were: 0.002161
Total seconds used by the ODBC ACCESS engine were 988.840234

ODBC: COMMIT performed on connection 6.
ERROR: CLI disconnect failed: [Oracle][ODBC]Invalid transaction state.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
 
Please guide me 

Re: SAS DI Table loader oracle

LinusH — Mon, 08 Mar 2021 15:09:43 GMT

What libname and data set options are in effect for this step?

Try to add this temporary to the code so you might see more in the log:

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

Re: SAS DI Table loader oracle

Kashishjoker — Wed, 17 Mar 2021 14:02:56 GMT

Hello,

Thanks for revert.
As suggested I have added the option, can you please suggest now.

ODBC_168: Executed: on connection 6
Prepared statement ODBC_124

ODBC: ROLLBACK performed on connection 6.
NOTE: There were 1441748 observations read from the data set WORK.W8BZ164Z.
NOTE: 1441747 observations added.
NOTE: The data set table has . observations and 40 variables.
ODBC: COMMIT performed on connection 6.

Summary Statistics for ODBC are:
Total SQL execution seconds were: 978.921050
Total SQL prepare seconds were: 0.002722
Total SQL describe seconds were: 0.002161
Total seconds used by the ODBC ACCESS engine were 988.840234

ODBC: COMMIT performed on connection 6.
ERROR: CLI disconnect failed: [Oracle][ODBC]Invalid transaction state.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 16:28.87
user cpu time 19.62 seconds
system cpu time 1.18 seconds
memory 107196.37k
OS Memory 125096.00k
Timestamp 05/03/2021 06:26:02 PM
Step Count 89 Switch Count 1
Page Faults 0
Page Reclaims 291072
Page Swaps 0
Voluntary Context Switches 174
Involuntary Context Switches 429
Block Input Operations 8
Block Output Operations 16

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2021-03-05T18:26:02,165+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 1131565056| _DISARM| 19304448| _DISARM| 12| _DISARM| 36| _DISARM| 24| _DISARM| 706637512| _DISARM| 20.800000|
_DISARM| 988.870710| _DISARM| 1930567173.295236| _DISARM| 1930568162.165946| _DISARM| 19.620000| _DISARM| | _ENDDISARM
MPRINT(ETLS_LOADER): ;

Re: SAS DI Table loader oracle

Kashishjoker — Mon, 08 Mar 2021 15:57:29 GMT

LIBNAME *** ODBC DATAsrc=ODBC SCHEMA=***** USER=*** PASSWORD="*****" INSERTBUFF=32767;

Re: SAS DI Table loader oracle

LinusH — Thu, 18 Mar 2021 07:01:20 GMT

And there is no data set options in your proc append?

Two ideas:

you seem to read one more record than gets inserted. Is there a data problem in that record? What constraints do you have on the oracle table?
You might hit some kind of limit in Oracle, check with your DBA to see if he/she can find anything in the Oracle logs that could help.