4gl error with create temp table stmt using IDS 94

Hi, our vendor has a 4gl program that has started to fail after we upgraded to IDS 94 and c4gl 7.32hc2, csdk 2.81 and esaql-c 9.53hc2. The program generates an error number 271 and 131 when it is creating a temp table. The syntax is as follows and the error line is 424. 417 when xrectype = "C" 418 call process_C_record() 419 insert into HoldAllFile 420 (rectype,groupno,reportper,typecode,versionno,ssn, 421 systemcode,cont,tax_status,invest_mgr,rate_opt) 422 values 423 (xrectype,CRec.groupno,CRec.reportper,CRec.typecode, 424 CRec.versionno,CRec.ssn,CRec.systemcode,CRec.cont, 425 CRec.tax_status,CRec.invest_mgr,CRec.rate_opt) (might the error be in line 418?) The table HoldAllFile is Create temp table HoldAllFile (rectype char(1),...snip... mycol char(3)) We have set the parameter in the onconfig for temp spaces 512 meg on different drives. DBSPACETEMPtempdbs941:tempdbs942:tempdbs943:tempdbs944:tempdbs945:tempdbs946:tempdbs947:tem pdbs948:tempdbs949:tempdbs9410 I did not see any unix broadcast complaining that /tmp or any file system was full, so I don't think it was actually trying to create a flat file as a temp file. In the past we did not have any issues with temp space being exceeded. Aside from creating larger more temp spaces or more of them, is there another option or am I overlooking a parameter that the vendor may be setting? The .profile does not have dbspacetemp set. onstat -d shows temp tables as free. onstat -D shows no usage. It appears thetemp db spaces for this program are not being used. Any ideas? Thanks in advance, Doug

My guess is the HoldAllFile temp table is being stored in the rootdbs because it was not defined WITH NO LOG. Is your rootdbs full when the query fails? If so, try a CREATE TEMP TABLE HoldAllFile ( ...snip... ) WITH NO LOG; and see if your temp dbspaces see some activity. Andrew Ford -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Doug Fossmeyer Sent: Wednesday, June 28, 2006 5:50 PM To: ids@iiug.org Subject: 4gl error with create temp table stmt using IDS 94 [7068] Hi, our vendor has a 4gl program that has started to fail after we upgraded to IDS 94 and c4gl 7.32hc2, csdk 2.81 and esaql-c 9.53hc2. The program generates an error number 271 and 131 when it is creating a temp table. The syntax is as follows and the error line is 424. 417 when xrectype = "C" 418 call process_C_record() 419 insert into HoldAllFile 420 (rectype,groupno,reportper,typecode,versionno,ssn, 421 systemcode,cont,tax_status,invest_mgr,rate_opt) 422 values 423 (xrectype,CRec.groupno,CRec.reportper,CRec.typecode, 424 CRec.versionno,CRec.ssn,CRec.systemcode,CRec.cont, 425 CRec.tax_status,CRec.invest_mgr,CRec.rate_opt) (might the error be in line 418?) The table HoldAllFile is Create temp table HoldAllFile (rectype char(1),....snip... mycol char(3)) We have set the parameter in the onconfig for temp spaces 512 meg on different drives. DBSPACETEMPtempdbs941:tempdbs942:tempdbs943:tempdbs944:tempdbs945:tempdbs946:tempdb s947:tempdbs948:tempdbs949:tempdbs9410 I did not see any unix broadcast complaining that /tmp or any file system was full, so I don't think it was actually trying to create a flat file as a temp file. In the past we did not have any issues with temp space being exceeded. Aside from creating larger more temp spaces or more of them, is there another option or am I overlooking a parameter that the vendor may be setting? The .profile does not have dbspacetemp set. onstat -d shows temp tables as free. onstat -D shows no usage. Itappears the temp db spaces for this program are not being used. Any ideas? Thanks in advance, Doug ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Doug: In order to use the space provided in the DBSPACETEMP configuration parameter your temp tables must specific the clause "with no log". If you omit = this then you the temp tables will be placed in the dbspace where the database was created. Only non-logging temp tables can be placed in temp dbspaces. Create temp table HoldAllFile (rectype char(1),....snip... mycol char(3)) WITH NO LOG; John Miller = "Doug Fossmeyer" = <DougF@SpokaneSch = ools.org> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect 4gl error with create temp table= 06/28/2006 02:49 stmt using IDS 94 [7068] = PM = = = Please respond to = ids@iiug.org = = = Hi, our vendor has a 4gl program that has started to fail after we upgr= aded to IDS 94 and c4gl 7.32hc2, csdk 2.81 and esaql-c 9.53hc2. The program generates an error number 271 and 131 when it is creating a= temp table. The syntax is as follows and the error line is 424. 417 when xrectype =3D "C" 418 call process_C_record() 419 insert into HoldAllFile 420 (rectype,groupno,reportper,typecode,versionno,ssn, 421 systemcode,cont,tax_status,invest_mgr,rate_opt) 422 values 423 (xrectype,CRec.groupno,CRec.reportper,CRec.typecode, 424 CRec.versionno,CRec.ssn,CRec.systemcode,CRec.cont, 425 CRec.tax_status,CRec.invest_mgr,CRec.rate_opt) (might the error be in line 418?) The table HoldAllFile is Create temp table HoldAllFile (rectype char(1),....snip... mycol char(3)) We have set the parameter in the onconfig for temp spaces 512 meg on different drives. DBSPACETEMPtempdbs941:tempdbs942:tempdbs943:tempdbs944:tempdbs945:tempdbs946:tempd= bs947:tempdbs948:tempdbs949:tempdbs9410 I did not see any unix broadcast complaining that /tmp or any file syst= em was full, so I don't think it was actually trying to create a flat file as = a temp file. In the past we did not have any issues with temp space being exceeded. Aside from creating larger more temp spaces or more of them, is there another= option or am I overlooking a parameter that the vendor may be setting? The .profile does not have dbspacetemp set. onstat -d shows temp tables as free. onstat -D shows no usage. It appea=rs the temp db spaces for this program are not being used. Any ideas? Thanks in advance, Doug ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Doug: Here is the extract from The Administrators Guide If you use the CREATE TEMP and SELECT...INTO TEMP SQL statements and DBSPACETEMP has been set: LOGGING dbspaces in the list are used to create the tables that specify or imply the WITH LOG clause. NON-LOGGING temporary dbspaces in the list are used to create the tables that specify the WITH NO LOG clause. This was covered recently by Art and as you can see it depends on whether or not the dbspace when it is created is defined as Temporary or Not. Any non temporary dbspace listed in the DBSPACETEMP directory will be used for logged temporary table Barry -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: 28 June 2006 23:01 To: ids@iiug.org Subject: Re: 4gl error with create temp table stmt usin.... [7070] Doug: In order to use the space provided in the DBSPACETEMP configuration parameter your temp tables must specific the clause "with no log". If you omit = this then you the temp tables will be placed in the dbspace where the database was created. Only non-logging temp tables can be placed in temp dbspaces. Create temp table HoldAllFile (rectype char(1),.....snip... mycol char(3)) WITH NO LOG; John Miller = "Doug Fossmeyer" = <DougF@SpokaneSch = ools.org> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect 4gl error with create temp table= 06/28/2006 02:49 stmt using IDS 94 [7068] = PM = = = Please respond to = ids@iiug.org = = = Hi, our vendor has a 4gl program that has started to fail after we upgr= aded to IDS 94 and c4gl 7.32hc2, csdk 2.81 and esaql-c 9.53hc2. The program generates an error number 271 and 131 when it is creating a= temp table. The syntax is as follows and the error line is 424. 417 when xrectype =3D "C" 418 call process_C_record() 419 insert into HoldAllFile 420 (rectype,groupno,reportper,typecode,versionno,ssn, 421 systemcode,cont,tax_status,invest_mgr,rate_opt) 422 values 423 (xrectype,CRec.groupno,CRec.reportper,CRec.typecode, 424 CRec.versionno,CRec.ssn,CRec.systemcode,CRec.cont, 425 CRec.tax_status,CRec.invest_mgr,CRec.rate_opt) (might the error be in line 418?) The table HoldAllFile is Create temp table HoldAllFile (rectype char(1),.....snip... mycol char(3)) We have set the parameter in the onconfig for temp spaces 512 meg on different drives. DBSPACETEMPtempdbs941:tempdbs942:tempdbs943:tempdbs944:tempdbs945:tempdbs946:tempd= bs947:tempdbs948:tempdbs949:tempdbs9410 I did not see any unix broadcast complaining that /tmp or any file syst= em was full, so I don't think it was actually trying to create a flat file as = a temp file. In the past we did not have any issues with temp space being exceeded. Aside from creating larger more temp spaces or more of them, is there another= option or am I overlooking a parameter that the vendor may be setting? The ..profile does not have dbspacetemp set. onstat -d shows temp tables as free. onstat -D shows no usage. It appea= rs the temp db spaces for this program are not being used. Any ideas? Thanks in advance, Doug ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. = ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the originator of the message. This footer also confirms that this e-mail message has been scanned for the presence of computer viruses. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Bedfordshire Pilgrims Housing Association. Scanning of this message and addition of this footer is performed by SurfControl E-mail Filter software in conjunction with virus detection software.