ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL9010 Running SQL from CL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL9010 Running SQL from CL

    Hi

    I created a SQL in TXT format.
    and try to run with CL with Command RUNSQLSTM, but got an error SQL9010.
    Any 1 had such experience can share please?

    thanks

  • #2
    Re: SQL9010 Running SQL from CL

    Your SQL Statement may not be correct.
    Check the listing print file which is written.
    Or run your stored SQL statement one after the other to find out which one is wrong.

    Birgitta

    Comment


    • #3
      Re: SQL9010 Running SQL from CL

      Like Birgitta said, look at your spool file that was generated for the SQL statements. It's usually some stupid error thats hard to spot, like a missing parenthesis. Here's a dummy test to show what it looks like:

      Code:
                                        * * * * *  E N D  O F  S O U R C E  * * * * *           
       SQL7950   0       2  Position 1 Table PROJECTH created in PROJECTSQL.                    
       SQL7965   0      17  Position 1 LABEL ON for PROJECTH in PROJECTSQL completed.           
       SQL7965   0      20  Position 1 LABEL ON for PROJECTH in PROJECTSQL completed.           
       SQL7965   0      30  Position 1 LABEL ON for PROJECTH in PROJECTSQL completed.           
       5761SS1 V6R1M0 080215               Run SQL Statements             FILEBUILD             
       Record  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 
       SQL0104  30      46  Position 17 Token was not valid.                 
                            Valid tokens: ) ,.                                                  
       SQL7961   0          Rollback completed.                                                 
                           Message Summary                                                      
         Total     Info   Warning      Error   Severe  Terminal                                 
             6        5         0          0        1         0                                 
       30 level severity errors found in source                                                 
                                       * * * * *  E N D  O F  L I S T I N G  * * * * *
      This says there's an error at line 46. If we look at the range of statements around 46, we get this:

      Code:
            40                                                               
            41 CREATE TABLE PROJECTD (                                       
            42        ACTIVITY   DECIMAL(9,0) PRIMARY KEY                    
            43                   GENERATED ALWAYS AS IDENTITY                
            44                   (START WITH 1, INCREMENT BY 1),             
      [COLOR=#ffa500]    45        PROJECTID  DECIMAL(9,0) NOT NULL, [/COLOR]             
      [COLOR=#ff0000]    46 RCDFMT RPROJECTD;  [/COLOR]

      Statement 46 is actually correct, but if we back up one statement, we see that statement 45 does not have a closing parenthesis. It has a comma, which means that line 46 should have the next column being described. So the actual error is on statement 45, which should be set to this:

      45 PROJECTID DECIMAL(9,0) NOT NULL)
      Michael Catalani
      IS Director, eCommerce & Web Development
      Acceptance Insurance Corporation
      www.AcceptanceInsurance.com
      www.ProvatoSys.com

      Comment


      • #4
        Re: SQL9010 Running SQL from CL

        hi

        I have checked the statement is right on strsql.
        Any limitation on the SQL TXT file?
        the statement is like below:
        create table lib\src as (select * from A union select * from B) with data.

        cheers

        Comment


        • #5
          Re: SQL9010 Running SQL from CL

          ... must be LIB/SRC

          ... and source code is only allowed until possition 80 in the member, except you are on release 6.1 or higher and changed the option MARGINS in the command RUNSQLSTM to anything else higher than 80.

          Birgitta

          Comment


          • #6
            Re: SQL9010 Running SQL from CL

            Try using the DB2
            http://publib.boulder.ibm.com/infoce...db2utility.htm

            It would look like this in a CL except with the -f file instead of SQL Code
            [CODE] QSH CMD('DB2 "CREATE TABLE LIB.TBL AS (SELECT * FROM LIB.TBL) WITH DATA"')

            Comment

            Working...
            X