Informix

 View Only
  • 1.  create table taby as select * from tabx PANIC informix server

    Posted Fri May 27, 2022 12:08 PM
    On informix 12.10 and 14.10 can create table as select from.
    I used the informix demo DB to test.
    But when send two create table as select from two SQL at same request like below PANIC the informix server:
      create table itemxx as select * from items;create table custxx as select *
        from customer
     
    The online log message:
    13:01:03  Assert Failed: No Exception Handler
    13:01:03  IBM Informix Dynamic Server Version 14.10.FC6WE
    13:01:03   Who: Session(99, informix@192.168.125.103, 18132, 700020000782ab8)
                    Thread(453, sqlexec, 700020000745680, 1)
                    File: mtex.c Line: 564
    13:01:03   Results: Exception Caught. Type: MT_EX_OS, Context: mem
    13:01:03   Action: Please notify IBM Informix Technical Support.
    13:01:03   See Also: /home/ids14fc6/tmp/af.5ad5b0e
    13:01:18  Thread ID 453 will NOT be suspended because
              it is holding a buffer.
    13:01:18   See Also: /home/ids14fc6/tmp/af.5ad5b0e
    13:01:18  mtex.c, line 564, thread 453, proc id 12911096, No Exception Handler.
    13:01:18  The Master Daemon Died
    13:01:18  PANIC: Attempting to bring system down

    The af.xxxx file is attached.

    Regards.

    Philip Yeh.


    #Informix


  • 2.  RE: create table taby as select * from tabx PANIC informix server
    Best Answer

    IBM Champion
    Posted Fri May 27, 2022 12:40 PM
    Philip:

    We cannot see attachments here nor in the email gateway. You should open a support case with IBM.

    That said, I tried it on my 14.10.FC8 server with no trouble:


    > create table mo_testxx as select * from mo_test;

    12 row(s) retrieved into table.

    > select count(*) from mo_testxx;


         (count(*))  

                 12

    1 row(s) retrieved.

    >
    $ onstat -m

    IBM Informix Dynamic Server Version 14.10.FC8DE -- On-Line -- Up 5 days 04:35:25 -- 2268292 Kbytes
    2022-05-27 12:38:55

    Message Log File: /opt/informix/infmx/tmp/online.log

    ...
    12:35:01  Checkpoint Completed:  duration was 0 seconds.
    12:35:01  Fri May 27 - loguniq 3120, logpos 0x104e018, timestamp: 0x44ce3ac0 Interval: 51012

    12:35:01  Maximum server connections 2  
    12:35:01  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 15, Llog used 6

    No af here.



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: create table taby as select * from tabx PANIC informix server

    Posted Sat May 28, 2022 03:33 AM
      |   view attached

    Dear Art,

    Thank you for your testing. The problem occurs when create table as select run two create at one SQL request, like below:

    create table itemxx as select * from items; create table custxx as select * from customer


    When use the informix dbaccessdemo7 created DB to run above SQL. 
    I used the Informix CSDK ConnectTest.exe to send the above test SQL at one line delimiter by ; 

    The af. file was attached again.

    Best Regards.




    ------------------------------
    Philip Yeh
    ------------------------------

    Attachment(s)

    zip
    af.zip   340 KB 1 version


  • 4.  RE: create table taby as select * from tabx PANIC informix server

    IBM Champion
    Posted Sun May 29, 2022 01:11 PM
    Philip:

    Still no problem here:

    $ dbaccess stores_demo -

    Database selected.

    > create table itemxx as select * from items; create table custxx as select * from customer;

    67 row(s) retrieved into table.

    28 row(s) retrieved into table.

    > info columns for itemxx;

    Column name          Type                                    Nulls

    item_num             smallint                                yes
    order_num            integer                                 yes
    stock_num            smallint                                yes
    manu_code            char(3)                                 yes
    quantity             smallint                                yes
    total_price          money(8,2)                              yes

    > info columns for custxx;

    Column name          Type                                    Nulls

    customer_num         serial                                  no
    fname                char(15)                                yes
    lname                char(15)                                yes
    company              char(20)                                yes
    address1             char(20)                                yes
    address2             char(20)                                yes
    city                 char(15)                                yes
    state                char(2)                                 yes
    zipcode              char(5)                                 yes
    phone                char(18)                                yes
    >



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: create table taby as select * from tabx PANIC informix server

    IBM Champion
    Posted Sun May 29, 2022 06:14 PM
    I see the same issue when I try this through ConnectTest Demo.  I am using CSDK 4.10.FC14, against 14.10.FC3DE.

    I don't get it when trying 2 simple selects, because the connecttest demo says that you can't submit multiple select statements in a multi-statement prepare.

    You'll see from your own assert file that BOTH statements were sent to the database server.

    Current SQL statement (1) :
    create table itemxx as select * from items;create table custxx as select *
    from customer

      The problem is that this should not have made it through the parsing in the utility, and so the problem is likely with connecttest demo, and not the Informix engine.  As Art has shown, this works in dbaccess.

    I am able to create two tables in one line, such as:  create table mw1(a serial); create table mw2(a serial);
    But I suspect that the syntax of "create table...as select..." is just not supported through the utility.  I don't have CSDK 4.50 installed for me to test under that.






    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------