Re: Create table with USING clause not working

On 06-Jun-2011 12:29 , Tom E Stieger wrote:

I was trying to create a backup of some data before a did a mass
update and thought this would work:

create table mylib.FILE_BACKUP as
(
select
rthd, opseq, opdsc
from
amflibp.rtghdr join
amflibp.rtgopr
using (STID, RTID, RTVR)
) with data
;

The underlying SQL for the select statement works, but the create
table causes the following errors:

SQL State: 58004
Vendor Code: -901
Message: [SQL0901] SQL system error. Cause . . . . . : An SQL system
error has occurred...The previous message identifier was MCH3601.
Internal error type 6101 has occurred...

MCH3601: Pointer not set for location referenced.

When I change my select statement to use on criteria in the join it
works just fine.

create table mylib.FILE_BACKUP as
(
select
rthd, opseq, opdsc
from
amflibp.rtghdr A join
amflibp.rtgopr B
on A.stid=B.stid and A.rtid = B.rtid and A.rtvr = B.rtvr
) with data
;

Does anyone know why the USING clause doesn't work with the "CREATE
TABLE as (select statement) with data"?
We are at 6.1.

Since the SQL0901 is little like the CPF9999 logged after some prior failure, but for processing within the SQL processor itself. While the failing statement is important, the details of the prior msgMCH3601 are generally the most informative for identifying any match to any known origin of the problem. FWiW there is a "database-wide logger" available to collect some information [including MSGS details] about such errors:
https://www-304.ibm.com/support/docview.wss?uid=nas345d61b8ab496f6c98625762100005f9d
"CREATE TABLE QRECOVERY.QSQ901S ... 'SQL0901 INTERNAL ERROR TRACKER TABLE'"

Regardless, probably?:
https://www-304.ibm.com/support/docview.wss?uid=nas2a4ed7c70e17eaa7b8625782a0041f0b9
"SE46714 - OSP-DB-OTHER-F/QSQRCHK-T/QSQRCHK-MSGMCH3601 MESSAGE ON CREATE TABLE WITH SELECT"
R610 SI42565 1000 [1000 means cumulative *none; r610=v6r1]

Regards, Chuck