Hi guys,

I have a code which reads an Master SQL DB table and writes the output to another Master SQL DB table:

TABLE TABLE1
PRINT *
WHERE ....
ON TABLE HOLD AS TABLE2 FORMAT SQLNEZ
END

TABLE1 and TABLE2 are both Netezza tables.

When I run this i got the following error:

(FOC1400) SQLCODE IS 11403 (HEX: 00002C8B) XOPEN: HY014
: (11403) [HY014] Driver can process one query at one time
(FOC1415) COMMIT WORK ERROR.
(FOC1400) SQLCODE IS 11403 (HEX: 00002C8B) XOPEN: HY014
: (11403) [HY014] Driver can process one query at one time
(FOC1415) COMMIT WORK ERROR.


I'm using Netezza JDBC driver.

Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,

quote:

FORMAT SQLNEZ

Hi as2010

I am following up with you regarding your question about - COMMIT ERROR Netezza tables.

After a bit of research in the IBI knowledge base I found the following information.

When SET EMGSRV=OFF is set to prevent error messages from displaying to the
screen, a test of the Netezza adapter fails. If SET EMGSRV=ON is used, it works
as expected.

I have also found a similar case with the following suggestions. Is there a JOIN in your request? If you are using a JOIN with UNIQUE try changing to MULTIPLE.

Under certain circumstances, the reporting server will split a JOIN'ed request
into more than one request. The Netezza Driver will only allow one active
cursor to be open on one connection.

The solution/workaround was:
The access file contained KEYS=0 so the JOIN was not optimized and sent 2
requests. If the table contains a key, KEYS should not be 0.

If these suggestions do not provide any assistance it might be worth opening a case.

Please provide an update if you do find a resolution with any of the above.

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator
Information Builders Inc.

Hi,

My code is like this:

SQL
SELECT
* FROM TDW00217
WHERE Z_INICIO <= '20150622' AND Z_FIM > '20150622';
TABLE
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS WF_TDW217_RDT0295_20150623 FORMAT SQLNEZ
END

Log :
(FOC324) THE PARAMETER TO BE SET IS NOT RECOGNIZED: XRERTIEVAL
17.21.40.692996 SELECT "SQLCOR01"."N_PE", "SQLCOR01"."N_EMPREGADO",
17.21.40.693016 "SQLCOR01"."C_USERID", "SQLCOR01"."C_FUNZ_EMPD_GCX",
17.21.40.693023 "SQLCOR01"."C_UO_EMPD", "SQLCOR01"."C_MNEM_EGC_EMPD",
17.21.40.693030 "SQLCOR01"."X_PAIS_CLCZ_EMPD", "SQLCOR01"."Z_INICIO",
17.21.40.693037 "SQLCOR01"."Z_FIM", "SQLCOR01"."TS_CARREGAMENTO",
17.21.40.693044 "SQLCOR01"."I_ELIZ_LOGC", "SQLCOR01"."Z_ELIZ_LOGC",
17.21.40.693051 "SQLCOR01"."TS_ACTZ_ULT" FROM BDPPDW.TIG00217 "SQLCOR01" WHERE
17.21.40.693058 "SQLCOR01"."Z_INICIO" <= DATE['2015-06-22') AND
17.21.40.693064 "SQLCOR01"."Z_FIM" > DATE('2015-06-22');
17.21.40.764823 DROP TABLE WF_TDW217_RDT0295_20150623;
(FOC1400) SQLCODE IS 11403 (HEX: 00002C8B) XOPEN: HY014
: (11403) [HY014] Driver can process one query at one time
(FOC1415) COMMIT WORK ERROR.
17.21.40.871404 CREATE TABLE WF_TDW217_RDT0295_20150623( "FOCLIST" INTEGER
17.21.40.871419 NOT NULL ,"E01" DECIMAL(13, 0) NOT NULL ,"E02" DECIMAL(7, 0)
17.21.40.871427 NOT NULL ,"E03" CHAR (8) NOT NULL ,"E04" CHAR (6) NOT NULL ,
17.21.40.871434 "E05" CHAR (5) NOT NULL ,"E06" CHAR (3) NOT NULL ,"E07" CHAR
17.21.40.871441 (80) NOT NULL ,"E08" DATE NOT NULL ,"E09" DATE NOT NULL ,
17.21.40.871448 "E10" TIMESTAMP NOT NULL ,"E11" CHAR (1) NOT NULL ,"E12" DATE
17.21.40.871456 NOT NULL ,"E13" TIMESTAMP NOT NULL , PRIMARY KEY ("FOCLIST") );
(FOC1400) SQLCODE IS 11403 (HEX: 00002C8B) XOPEN: HY014
: (11403) [HY014] Driver can process one query at one time
(FOC1415) COMMIT WORK ERROR.
1
0 NUMBER OF RECORDS IN TABLE= 922 LINES= 921
0


The final destination table has no records, it was suposed to have about 10K.

TDW00217.acx: SEGNAME=TDW00217, TABLENAME=BDPPDW.TIG00217, CONNECTION=BDPPDW, KEYS=2, $
TDW00217.mas:
FILENAME=TDW00217, SUFFIX=SQLNEZ , $
SEGMENT=TDW00217, SEGTYPE=S0, $
FIELDNAME=N_PE, ALIAS=N_PE, USAGE=P12, ACTUAL=P6, $
FIELDNAME=N_EMPREGADO, ALIAS=N_EMPREGADO, USAGE=P7, ACTUAL=P4, $
FIELDNAME=C_USERID, ALIAS=C_USERID, USAGE=A8, ACTUAL=A8, $
FIELDNAME=C_FUNZ_EMPD_GCX, ALIAS=C_FUNZ_EMPD_GCX, USAGE=A6, ACTUAL=A6, $
FIELDNAME=C_UO_EMPD, ALIAS=C_UO_EMPD, USAGE=A5, ACTUAL=A5, $
FIELDNAME=C_MNEM_EGC_EMPD, ALIAS=C_MNEM_EGC_EMPD, USAGE=A3, ACTUAL=A3, $
FIELDNAME=X_PAIS_CLCZ_EMPD, ALIAS=X_PAIS_CLCZ_EMPD, USAGE=A80, ACTUAL=A80, $
FIELDNAME=Z_INICIO, ALIAS=Z_INICIO, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=Z_FIM, ALIAS=Z_FIM, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=TS_CARREGAMENTO, ALIAS=TS_CARREGAMENTO, USAGE=HYYMDm, ACTUAL=HYYMDm, $
FIELDNAME=I_ELIZ_LOGC, ALIAS=I_ELIZ_LOGC, USAGE=A1, ACTUAL=A1, $
FIELDNAME=Z_ELIZ_LOGC, ALIAS=Z_ELIZ_LOGC, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=TS_ACTZ_ULT, ALIAS=TS_ACTZ_ULT, USAGE=HYYMDm, ACTUAL=HYYMDm, $

Hi Tamra,

I found a solution with help of Netezza DBA:
I need to set the FETCHSIZE and INSERTSIZE to "fit" the whole result set. For example if the query retrives 100 rows i need to set:
SQL SQLNEZ SET FETCHSIZE 100
SQL SQLNEZ SET INSERTSIZE 100
before the TABLE FILE.

Thanks for your collaboration.