How to resolve java.sql.SQLException: [SQL7008] table_name in database_name not valid for operation while sqoop export from hdfs to DB2

Question

I am trying to export a file from HDFS to a DB2 AS400 table using sqoop export and getting below error:

Caused by: java.sql.SQLException: [SQL7008] SCORE in OPIDSYS not valid for operation.

When researched online I got this link: http://www.websina.com/bugzero/faq/exception-as400.html

But I am not sure what Journaling means and my AS400 Architect also have no idea about that.

My Sqoop:

sqoop export --driver com.ibm.as400.access.AS400JDBCDriver --connect jdbc:as400://SERVER:0000/DB  --username xxx  --password 'xxx'  --table  DB.SCORE --direct --export-dir '/test/path/' --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-null-string '\\\\N' --input-null-non-string '\\\\N' --verbose

Sample hdfs data:

match,score

11,2

22,9

33,4

Some one who calls themself an AS400 architect should really know what journaling is. What you found when you were googling is most likely the correct answer. Here is a good article on what journaling is and what it does ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/rzaki/… — GenericDisplayName
– GenericDisplayName, Commented Aug 1, 2019 at 19:44

jmarkmurphy · Accepted Answer · 2019-08-02 14:29:07Z

SQL7008 has a number of causes, and the exact cause cannot be determined without seeing the reason code in the Secondary text of the message. One of those causes is that you are trying to use commitment control on a table that is not journaled.

Journaling is the method DB2 for i uses to record database transactions, and is used by commitment control to enable commit and rollback. In fact, this is true of record level access (READ, CHAIN, etc.), and SQL, though RLA defaults to no commitment control, and SQL defaults to using commitment control. If your tables are not journaled, you can still use SQL though.

When creating programs with embedded commitment control (CRTSQLRPG, CRTSQLRPGI, etc.) or using RUNSQLSTM, you need to make sure to use COMMIT(*NONE). The default is COMMIT(*CHG). The default with STRSQL is already COMMIT(*NONE).

If you are using SQL through a client like DBeaver or SQuirreL SQL client, you need to make sure the connection defaults to no commitment control. Specifically how to do this is different depending on whether you are using JDBC or ODBC to connect, but you should be able to find the specific connection properties for each.

If you really want to use commitment control (not a bad idea), then you will need to start journaling on all physical files (tables) in your database that you need protected by commitment control, and if that is not all of them you will need to remember to selectively turn off commitment control for those tables that are not journaled, the WITH NC clause on your INSERT, UPDATE, and DELETE statements will do that.

To turn on journaling you will need to ensure that you have a journal and a journal receiver. If they don't exist, then put them in the same library as your physical files. Create the receiver first with CRTJRNRCV, then create a journal with CRTJRN. CRTJRN will need the name of the receiver you created earlier. After you have a journal, you can then use STRJRNPF to start journaling on each physical file that you need to place under commitment control.

Collectives™ on Stack Overflow

How to resolve java.sql.SQLException: [SQL7008] table_name in database_name not valid for operation while sqoop export from hdfs to DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related