DB2-AS400 Series TDMServiceException

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We're getting an error while AS400 databases publish jobs which I've shared below:

Publish Job: 273544
Data Pool: Mxxxxx
TDMPublish, version: 4.9.416.0
DB Repository, version: 4.9.200.6
Start date and time: 2023/02/22 13:01:06
Publish to: TGT

Publish to: Database: gtrep and Schema: dbo using Profile: GTREP
Number of tables to publish: 1
Repeat count: 1
Publish folder: C:\ProgramData\CA\CA Test Data Manager Portal\\Jobs\

Status Table                          Table Location                 Seq Rows Comments 
------ -----                          ----- --------                 --- ---- -------- 
Publish JEM015BES                      KVKLIB                         1 1 

***********************************************

Logging progress at: 2023/02/22 13:01:10
0 row(s) inserted into table JEM015BES.  137 duplicates ignored.
0 generated duplicate row(s) removed for table JEM015BES
0 error(s) for table JEM015BES

ERROR: Publish failed for job 273544, An error occurred resolving TABLE: Jxxxxxxx COLUMN: Txxxxxx. The expression was <@seqlov(0,@sqllist(P~var_Mxxxxx_Source~,SELECT * FROM EMKLIB.EM015BES a ~Mxxxxxx_ref~ a.Txxxxxxx=b.TCID and b.kxxxxx=1)@,Txxxxxx)@>. The error is <resolve_meta_data_item: TDMServiceException: seqlov: SQLException: [SQL0501] Cursor CRSR0001 not open.>
End date and time: 2023/02/22 13:01:10

Environment

Release : 4.9

Resolution

You should have your DBA check the cursor status on table EMKLIB.EMO15BES.

https://www.ibm.com/docs/en/i/7.3?topic=errors-sql0501-cursor-crsr000x-not-open

SQL0501 - Cursor CRSR000x not open

Last Updated: 2022-05-03

For IBM® i Access ODBC: To return data when using embedded SQL in ILE programs, you must specify the compile option ACTGRP(*CALLER) and not the default of *NEW.

Verify that the program executes a return instead of an exit.

When the stored procedure program executes an exit instead of a return, you must set the Close SQL Cursor option to *ENDACTGRP. If the Close SQL Cursor option is set to *ENDMOD, the cursor will be closed before data is retrieved.

Also, verify that the CREATE PROCEDURE specifies the correct number of result sets. This is especially important when using array result sets.