Troubleshooting common core services

Use these solutions to resolve problems that you might encounter with connections.

IBM Db2 for z/OS: Error retrieving the schema list when you try to connect to a Db2 for z/OS server

IBM Db2 for z/OS: Error retrieving the schema list when you try to connect to a Db2 for z/OS server

When you test the connection to a Db2 for z/OS server and the connection cannot retrieve the schema list, you might receive the following error:

CDICC7002E: The assets request failed: CDICO2064E: The metadata for the column TABLE_SCHEM could not
 be obtained: Sql error: [jcc][10150] [10300][4.28.11] Invalid parameter: Unknown column name
 TABLE_SCHEM. ERRORCODE=-4460, SQLSTATE=null

Resolving the problem

On the Db2 for z/OS server, set the DESCSTAT subsystem parameter to No. For more information, see DESCRIBE FOR STATIC field (DESCSTAT subsystem parameter).

Can't run generated code from deprecated functionality to load data for an Informix connection

You receive a "DatabaseError: Execution failed on sql: ... unable to rollback" when running the code that is generated to load data from an Informix database when using the deprecated functionality and you try to query a table with an upper-case name.

Cause

The Informix database is configured for case-sensitive identifiers and you need to update the generated code.

Resolving the problem

If you can, change to using the code that is generated using the Flight service to load data from an Informix connection . If you want to continue using the code that is generated by the deprecated functionality, you need to edit the generated code as follows:

For Python, add the connection property 'DELIMIDENT=Y' to the connection and surround the upper-case identifier with double-quotes (""). Replace the following lines:

Informix_connection = jaydebeapi.connect('com.informix.jdbc.IfxDriver',
'{}://{}:{}/{}:user={};password={};'.format('jdbc:informix-sqli',
Informix_metadata['host'],
Informix_metadata['port'],
Informix_metadata['database'],
Informix_metadata['username'],
Informix_metadata['password']),
[Informix_metadata['username'],
Informix_metadata['password']])

query = 'SELECT * FROM informix.FVT_EMPLOYEE'

With:

Informix_connection = jaydebeapi.connect('com.informix.jdbc.IfxDriver',
'{}://{}:{}/{}:user={};password={};DELIMIDENT=Y;'.format('jdbc:informix-sqli',
Informix_metadata['host'],
Informix_metadata['port'],
Informix_metadata['database'],
Informix_metadata['username'],
Informix_metadata['password']),
[Informix_metadata['username'],
Informix_metadata['password']])

query = 'SELECT * FROM informix."FVT_EMPLOYEE"'

For Spark with Python, replace:

data_df_0 = sparkSession.read.format('jdbc') \
  .option('url', 'jdbc:informix-sqli://{}:{}/{}'.format(Informix_metadata['host'],Informix_metadata['port'],Informix_metadata['database'])) \
  .option('dbtable', 'informix.FVT_EMPLOYEE') \
  .option('user', Informix_metadata['username']) \
  .option('password', Informix_metadata['password']).load()
data_df_0.show(5)

With:

data_df_0 = sparkSession.read.format('jdbc') \
  .option('url', 'jdbc:informix-sqli://{}:{}/{}'.format(Informix_metadata['host'],Informix_metadata['port'],Informix_metadata['database'])) \
  .option('dbtable', 'informix."FVT_EMPLOYEE"') \
  .option('DELIMIDENT', 'Y') \
  .option('user', Informix_metadata['username']) \
  .option('password', Informix_metadata['password']).load()
data_df_0.show(5)

For R, add the connection property 'DELIMIDENT=Y' to the connection and surround all upper case names with double-quotes (""). Replace the following lines:

paste("jdbc:informix-sqli://", Informix_credentials[][["host"]], ":", Informix_credentials[][["port"]],
"/", Informix_credentials[][["database"]],
":user=", Informix_credentials[][["username"]],
";password=", Informix_credentials[][["password"]], ";", sep=""),
...
query <- "SELECT * FROM myschema.MY_TABLE"

With:

paste("jdbc:informix-sqli://", Informix_credentials[][["host"]], ":", Informix_credentials[][["port"]],
"/", Informix_credentials[][["database"]],
":user=", Informix_credentials[][["username"]],
";password=", Informix_credentials[][["password"]],";DELIMIDENT=Y", ";", sep=""),
...
query <- "SELECT * FROM myschema.\"MY_TABLE\""

Parent topic: Troubleshooting problems in Cloud Pak for Data