If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 AS400 iSeries error message CPF4204 typquery with CTE from sysibm.sysdummy1 table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 1
DB2 AS400 iSeries error message CPF4204 typquery with CTE from sysibm.sysdummy1 table

I am trying to understand how common table expressions (CTEs) I am trying to figure out why I am getting errors related to the following:

Details: ADO Error Code: 0x
Source: IBMDA400 Command
Description: PWS9801 Function rejected by user exit program SAFENET in PCSECLIB
Cause: User exit program SAFENET in library PCSECLIB called for the function and returned an indication that the function should not be done.

The SQL code behind this is trivial:

with cte1 (my_text,my_numcte1) as
(
select 'hello world1',1 from sysibm.sysdummy1 union
select 'hello world2',2 from sysibm.sysdummy1
),

/*
cte_unnecessary as
(
select * from a73com.f0005
),
*/


cte2 (my_numcte2,new_text,additional_text) as
(
select 1,'goodbye1','hello again1' from sysibm.sysdummy1 union
select 2,'goodbye2','hello again2' from sysibm.sysdummy1
)
select cte1.* ,cte2.*
from cte1 inner join cte2 on cte1.my_numcte1=cte2.my_numcte2

Note that I have commented out a section, so this query is selecting everything from the sysibm.sysdummy1 tables.

I get the error above for this seemingly trivial query.

When I remove the comment indicators by inserting another CTE that actually queries against a real library and table, then the query runs fine and returns the following:

MY_TEXT MY_NUMCTE1 MY_NUMCTE2 NEW_TEXT ADDITIONAL_TEXT
hello world1 1 1 goodbye1 hello again1
hello world2 2 2 goodbye2 hello again2

would anyone know why there has to be a we cannot create multiple CTEs that select from sysibm.sysdummy1 without having some other type of query against a real library and table?

For some more complex queries, where I am joining on real tables and also the sysibm.sysdummy1 table, (in some cases up to 12 CTEs linked together through various types of joins) I am seeing errors like the following:

Description: SQL0901: SQL System Error
Cause: A SQL system error has occurred. The current sql statement cannot be completed successfully. The error will not prevent other SQL statements from being processed. Previous messages indicate that the there is a problem with the SQL statement and SQL did not correctly diagnose the error. The previous message identifier was CPF4204. Internal error type 3002 has ocurred...


Anyone know what is happening? Similar queries on the MS-SQL server side using CTEs run fine.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On