201 syntax error: while creating external table

Posted in 2010

Summary

A user on IDS 11.50.FC3 (Solaris) got error -201 when running a CREATE EXTERNAL TABLE statement. Replies first pointed out a missing opening parenthesis after the USING keyword, but adding it still produced the syntax error at the CREATE EXTERNAL TABLE line itself. John Miller and Art Kagel then identified the real cause: external tables were only introduced in 11.50.xC6, so the feature doesn't exist in xC3. The fix is to upgrade; trial downloads from the IBM/Informix site were noted as already being xC6.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Server Administration, Platform-Specific Issues

RITESH KUMAR — Fri, 05 Mar 2010 08:06:49 — source: IIUG Forums & Mailing Lists

Hi, I want to create an external table to load data from flat file. I tried to execute below code in dbaccess terminal but i am getting 201 syntax error message. CREATE EXTERNAL TABLE ET_TEST ( id int, name char(10) ) USING DATAFILES ('DISK:/home/rtest.unl'), FORMAT 'DELIMITED', REJECTFILE '/home/rtest.rej', MAXERRORS 250, DELUXE ); Can any body let me know how to create an external table in IDS 11.50. I am using IDS 11.50 on SOLARIS machine. Thanks in advance......... Regards: Ritesh Kumar

↪ replying to RITESH KUMAR

Srinivasan R — Fri, 05 Mar 2010 08:17:23 — source: IIUG Forums & Mailing Lists

Missing '(' after USING keyword. Try CREATE EXTERNAL TABLE ET_TEST ( id int, name char(10) ) USING ( DATAFILES ('DISK:/home/rtest.unl'), FORMAT 'DELIMITED', REJECTFILE '/home/rtest.rej', MAXERRORS 250, DELUXE ); Srini "RITESH KUMAR" <ritesh.k@sonata- software.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject 201 syntax error: while creating 05/03/2010 13:36 external table [19240] Please respond to ids@iiug.org Hi, I want to create an external table to load data from flat file. I tried to execute below code in dbaccess terminal but i am getting 201 syntax error message. CREATE EXTERNAL TABLE ET_TEST ( id int, name char(10) ) USING DATAFILES ('DISK:/home/rtest.unl'), FORMAT 'DELIMITED', REJECTFILE '/home/rtest.rej', MAXERRORS 250, DELUXE ); Can any body let me know how to create an external table in IDS 11.50. I am using IDS 11.50 on SOLARIS machine. Thanks in advance......... Regards: Ritesh Kumar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to RITESH KUMAR

John Miller — Fri, 05 Mar 2010 08:18:34 — source: IIUG Forums & Mailing Lists

You are missing a parenthesis. You have the closing but not the opening parenthesis for the using clause. > CREATE EXTERNAL TABLE ET_TEST > ( > id int, > name char(10) > ) > USING ( ------------ Missing > DATAFILES ('DISK:/home/rtest.unl'), > FORMAT 'DELIMITED', > REJECTFILE '/home/rtest.rej', > MAXERRORS 250, > DELUXE > ); John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/05/2010 12:06:50 AM: > Hi, > > I want to create an external table to load data from flat file. > > I tried to execute below code in dbaccess terminal but i am getting > 201 syntax > error message. > > CREATE EXTERNAL TABLE ET_TEST > ( > id int, > name char(10) > ) > USING > DATAFILES ('DISK:/home/rtest.unl'), > FORMAT 'DELIMITED', > REJECTFILE '/home/rtest.rej', > MAXERRORS 250, > DELUXE > ); > > Can any body let me know how to create an external table in IDS 11.50. > > I am using IDS 11.50 on SOLARIS machine. > > Thanks in advance......... > > Regards: > Ritesh Kumar > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller

RITESH KUMAR — Fri, 05 Mar 2010 08:22:59 — source: IIUG Forums & Mailing Lists

After adding the opening parenthesis still i am getting same error. CREATE EXTERNAL TABLE ET_TEST # ^ # 201: A syntax error has occurred. # ( id int, name char(10) ) USING ( DATAFILES ('DISK:/home/rtest.unl'), FORMAT 'DELIMITED', REJECTFILE '/home/rtest.rej', MAXERRORS 250, DELUXE ); Ids version "IBM Informix Dynamic Server Version 11.50.FC3"

↪ replying to RITESH KUMAR

John Miller — Fri, 05 Mar 2010 08:26:27 — source: IIUG Forums & Mailing Lists

You are using the wrong version. External tables where first supported in version 11.50.xC6. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/05/2010 12:23:00 AM: > After adding the opening parenthesis still i am getting same error. > > CREATE EXTERNAL TABLE ET_TEST > # ^ > # 201: A syntax error has occurred. > # > ( > id int, > name char(10) > ) > USING ( > DATAFILES ('DISK:/home/rtest.unl'), > FORMAT 'DELIMITED', > REJECTFILE '/home/rtest.rej', > MAXERRORS 250, > DELUXE > ); > > Ids version "IBM Informix Dynamic Server Version 11.50.FC3" > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller

RITESH KUMAR — Fri, 05 Mar 2010 08:32:17 — source: IIUG Forums & Mailing Lists

Thanks John, Please let me know can I create external table in "Informix Dynamic Server Enterprise and Developer Edition" Trial edition? I want to create one sample to load data using external table. Could you please suggest me and IDS version which I can download and test this? Regards: Ritesh Kumar

↪ replying to RITESH KUMAR

Obnoxio The — Fri, 05 Mar 2010 08:36:19 — source: IIUG Forums & Mailing Lists

RITESH KUMAR wrote: > Thanks John, > > Please let me know can I create external table in "Informix Dynamic Server > Enterprise and Developer Edition" Trial edition? > > I want to create one sample to load data using external table. > Could you please suggest me and IDS version which I can download and test > this? I think there might have been a clue in the words "IDS 11.50.xC6" ... -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to RITESH KUMAR

Art Kagel — Fri, 05 Mar 2010 11:22:06 — source: IIUG Forums & Mailing Lists

What fixpack sub-version are you running? External table support is first available in 11.50.xC6. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 5, 2010 at 3:06 AM, RITESH KUMAR <ritesh.k@sonata-software.com>wrote: > Hi, > > I want to create an external table to load data from flat file. > > I tried to execute below code in dbaccess terminal but i am getting 201 > syntax > error message. > > CREATE EXTERNAL TABLE ET_TEST > ( > id int, > name char(10) > ) > USING > DATAFILES ('DISK:/home/rtest.unl'), > FORMAT 'DELIMITED', > REJECTFILE '/home/rtest.rej', > MAXERRORS 250, > DELUXE > ); > > Can any body let me know how to create an external table in IDS 11.50. > > I am using IDS 11.50 on SOLARIS machine. > > Thanks in advance......... > > Regards: > Ritesh Kumar > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747bf6c7ddb5d04810beceb

↪ replying to RITESH KUMAR

Art Kagel — Fri, 05 Mar 2010 11:36:29 — source: IIUG Forums & Mailing Lists

11.50.xC6 (or later, but that's the latest right now). You should be able to get a trial from the IBM web site, I see that the currently downloadable trial editions are all 11.50.xC6, so you are good to go. Start at www.informix.com and click on the "Trials and demos" button in the box at the upper left. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 5, 2010 at 3:32 AM, RITESH KUMAR <ritesh.k@sonata-software.com>wrote: > Thanks John, > > Please let me know can I create external table in "Informix Dynamic Server > Enterprise and Developer Edition" Trial edition? > > I want to create one sample to load data using external table. > Could you please suggest me and IDS version which I can download and test > this? > > Regards: > Ritesh Kumar > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747bf6cf7d10104810c1f99

201 syntax error: while creating external table

Glossary

Your privacy choices