EXTERNAL TABLE issue?
A CREATE EXTERNAL TABLE statement using FORMAT 'FIXED' worked fine run directly as SQL, but failed with error -26180 on the column definition when the same statement was wrapped in a stored procedure (SPL) on Informix 12.10.FC12 / Solaris 10; it had worked under 12.10.FC3. Another user reported similar external-table errors (-26157/-26169) after upgrading. IBM support reproduced it and filed a defect, but said no workaround exists for the FIXED format in an SPL (non-FIXED formats still work); they stated it was fixed for the next release, with no release date or public APAR number given at the time.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 12.10.FC12
Solaris 10 1/13
Consider the following SQL:
CREATE EXTERNAL TABLE ext_tbl(
data_row CHAR(978) EXTERNAL CHAR(978)
)
USING (
FORMAT 'FIXED',
DATAFILES ("DISK:/home/informix/ext_tbl.file")
);
It executes with no error reported.
Now consider the following SPL, which is the identical SQL in an SPL "wrapper":
CREATE PROCEDURE ext_tbl_tst()
CREATE EXTERNAL TABLE ext_tbl(
data_row CHAR(978) EXTERNAL CHAR(978)
)
USING (
FORMAT 'FIXED',
DATAFILES ("DISK:/home/informix/ext_tbl.file")
);
END PROCEDURE;
It compiles. When EXECUTED, it fails, reporting a -26180 error and specifying
"data_row" as the culprit.
To me it looks like a possible bug. But, I may be missing something obvious. I
hate to bother tech support with false alarms. So, just asking the Informix
community before I open a tech support case.
Thank you.
DG
P.S. I might also mention that this fragment is part of an SPL that has been in service for several years. We took it out of service about 9 months ago, and then, recently, tried to reinstate it, without success. In the intervening time, we upgraded from IDS 12.10.FC3 to 12.10.FC12.
It is probably a bug. We are testing Informix 12.10.FC12W1 in HP-UX B.11.31 ia64 and several Informix 4GL programs that have been working for years have also started to randomly (or so it seems atm) give errors with EXTERNAL TABLES. For us the errors are -26157 and -26169 .
We opened a tech support case a week ago. Got a message from them, today. They replicated the problem and are "filing a defect report", as they put it. They say they will have a work-around for us, soon. DG
We opened a tech support case two weeks ago. Got this message from the engineer, today: "I made some research and unfortunately could not find a workaround for this problem to achieve the same results. You can still use stored procedures to create external tables through other methods that do not use FIXED format, but there is no way to do so with the FIXED format." Perhaps, it will be fixed in a future release. I guess in the mean time, creating external tables with fixed format, from within a stored procedure, is unavailable. DG
Got a new response. No work-around is available. But, they also said the defect is already fixed for the next release. No release date specified. DG
Should have posted this response here: Got a new response. No work-around is available. But, they also said the defect is already fixed for the next release. No release date specified. DG
I too have been using external tables (we from the XPS world call it the ploader) and under 12.10.FC10 I'm seeing errors that never occurred prior. Really, really irritating. I'll open a PMR though now that I see others have had similar issues. Thanks - Mark Scranton The Mark Scranton Group
David, ask for the APAR/Issue number: it is normally a public informix on IBM's website
Thank you for your comment, Mr. Vercelleto. I did actually ask for the APAR at the time we were actively working the case. But, the engineer told me it was not available for public release at that time. (I haven't contacted tech support again, concerning this issue.) DG