Informix

 View Only
  • 1.  SELECT statement without the FROM clause

    Posted Mon March 16, 2020 10:18 PM
    Edited by System Fri January 20, 2023 04:26 PM
    When using the WITH clause, I accidentally found it to execute even though the FROM clause was omitted from the SELECT statement.
    Of course, only constants are output. It's the same as SQL Server, PostgreSQL and MySQL/MariaDB. (Is there any more?)
    It is available from 14.10.xC1 version. However, I was unable to find anything specified in the Knowledge Center for this feature.

    [informix@db2 ~]$ onstat -
    
    IBM Informix Dynamic Server Version 14.10.FC1DE -- On-Line -- Up 00:02:48 -- 599688 Kbytes
    
    [informix@db2 ~]$ dbaccess stores_demo -
    
    Database selected.
    
    > select 1;
    
    
     (constant)
    
              1
    
    1 row(s) retrieved.​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------
    #Informix


  • 2.  RE: SELECT statement without the FROM clause

    IBM Champion
    Posted Tue March 17, 2020 08:20 AM
    Yes. New feature for compatibility with the others.





  • 3.  RE: SELECT statement without the FROM clause

    Posted Tue March 17, 2020 09:14 AM
    Hi

    I found this in the knowledge center.
    "Every SELECT statement requires the FROM clause, whether or not any data source is required. "

    https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0160.htm#ids_sqs_0160

    As a result, this feature was not included in the documentation. Good to know that it has been introduced.

    ------------------------------
    Gábor Fekete
    Software Engineer
    Capital Systems
    Budapest
    ------------------------------



  • 4.  RE: SELECT statement without the FROM clause

    Posted Tue March 17, 2020 10:30 AM
    Gábor :
    Yes. Perhaps the documentation hasn't been updated. Thanks for sharing the link.
    I still used this function usefully only in WITH statement, but I will think about other uses such as in routine...etc.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 5.  RE: SELECT statement without the FROM clause

    IBM Champion
    Posted Tue March 17, 2020 11:22 AM
    Typically this is used like the Oracle construct:

    select 7 from dual;

    Or the Informix constructs:

    select 7 from sysmaster:sysdual;

    select 7 from systables where tabid = 1;

    Note that the new construct, without a FROM clause, is faster than the other two options in Informix. Witness these runs in sqlcmd in benchmark mode. I have highlighted the runtimes in RED:
    SQL[27]: select 7 from systables where tabid = 1;
    2020-03-17 11:18:12
    + select 7 from systables where tabid = 1;
    7
    Time: 0.002461
    SQL[28]: select 7 from sysmaster:sysdual;
    2020-03-17 11:18:20
    + select 7 from sysmaster:sysdual;
    7
    Time: 0.001722
    SQL[29]: select 7;
    2020-03-17 11:18:24
    + select 7;
    7
    Time: 0.000245

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.








  • 6.  RE: SELECT statement without the FROM clause

    IBM Champion
    Posted Tue March 17, 2020 11:31 AM
    So this is (probably) the quickest way to get current time, which is useful

    select current;

    2020-03-17 12:28:25.000

    Cheers
    Paul

    > Typically this is used like the Oracle construct:
    >
    > select 7 from dual;
    > Or the Informix constructs:
    >
    > select 7 from sysmaster:sysdual;
    > select 7 from systables where tabid = 1;
    > Note that the new construct, without a FROM clause, is faster than the
    > other two options in Informix. Witness these runs in sqlcmd in benchmark
    > mode. I have highlighted the runtimes in RED:SQL[27]: select 7 from
    > systables where tabid = 1;
    > 2020-03-17 11:18:12
    > + select 7 from systables where tabid = 1;
    > 7
    > Time: 0.002461
    > SQL[28]: select 7 from sysmaster:sysdual;
    > 2020-03-17 11:18:20
    > + select 7 from sysmaster:sysdual;
    > 7
    > Time: 0.001722
    > SQL[29]: select 7;
    > 2020-03-17 11:18:24
    > + select 7;
    > 7
    > Time: 0.000245
    >
    > Art
    > Art S. Kagel, President and Principal ConsultantASK Database
    > Managementwww.askdbmgt.com
    #Informix


  • 7.  RE: SELECT statement without the FROM clause

    IBM Champion
    Posted Tue March 17, 2020 11:38 AM
    You can also use the construct for more interesting and useful constants like:

    Current time:
    > select current;

    (expression)             

    2020-03-17 11:27:25.983

    1 row(s) retrieved.

    Translating a partnum from an onstat report:
    > select dbinfo( 'dbspace', '0x00212345');

    (constant)  

    plogdbs

    1 row(s) retrieved.

    Art
    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.








  • 8.  RE: SELECT statement without the FROM clause

    IBM Champion
    Posted Tue March 17, 2020 11:39 AM
    Yep, really giving examples of why this format is useful

    Cheers
    Paul

    > You can also use the construct for more interesting and useful constants
    > like:
    > Current time:
    >> select current;
    >
    > (expression)
    >
    > 2020-03-17 11:27:25.983
    >
    > 1 row(s) retrieved.
    >
    > Translating a partnum from an onstat report:
    >> select dbinfo( 'dbspace', '0x00212345');
    >
    > (constant)
    >
    > plogdbs
    >
    > 1 row(s) retrieved.
    >
    > ArtArt S. Kagel, President and Principal ConsultantASK Database
    > Managementwww.askdbmgt.com
    #Informix


  • 9.  RE: SELECT statement without the FROM clause

    Posted Tue March 17, 2020 12:38 PM
    Edited by System Fri January 20, 2023 04:20 PM
    It's simple to use and its performance is even better than before!
    When using sysdual or systables, the number of isreads in the sysptprof and syssesprof tables increases.
    However, when the SELECT statement without the from clause is executed, there is no change.

    After enabling SQLTRACE, I compared the use of sysdual with a select statement without the from clause with the 'onstat -g his' command.
    Statement # 22:     @ 0x4a2f20f0
    
     Database:        0x100004
     Statement text:
      select current
    
     Iterator/Explain
     ================
        ID   Left  Right Sender   Next   Est Cost   Est Rows   Num Rows    Partnum Type
         1      0      0      0      0          1          1          1          0 Coll Scan
    
     Statement information:
      Sess_id  User_id  Stmt Type        Finish Time    Run Time   TX Stamp   PDQ
      286      1001     SELECT           01:09:18       0.0000     9f7e7f5    0
    
     Statement Statistics:
      Page       Buffer     Read       Buffer     Page       Buffer     Write
      Read       Read       % Cache    IDX Read   Write      Write      % Cache
      0          0          0.00       0          0          0          0.00
    
      Lock       Lock       LK Wait    Log        Num        Disk       Memory
      Requests   Waits      Time (S)   Space      Sorts      Sorts      Sorts
      0          0          0.0000     0.000 B    0          0          0
    
      Total      Total      Avg        Max        Avg        I/O Wait   Avg Rows
      Executions Time (S)   Time (S)   Time (S)   IO Wait    Time (S)   Per Sec
      1          0.0000     0.0000     0.0000     0.000000   0.000000   22702.0926
    
      Estimated  Estimated  Actual     SQL        ISAM       Isolation  SQL
      Cost       Rows       Rows       Error      Error      Level      Memory
      1          1          1          0          0          CR         12360
    
    
    
    Statement # 36:     @ 0x4a2f2160
    
     Database:        0x100004
     Statement text:
      select current from sysdual
    
     Iterator/Explain
     ================
        ID   Left  Right Sender   Next   Est Cost   Est Rows   Num Rows    Partnum Type
         1      0      0      0      0          2          1          1         92 Seq Scan
    
     Statement information:
      Sess_id  User_id  Stmt Type        Finish Time    Run Time   TX Stamp   PDQ
      287      1001     SELECT           01:15:03       0.0001     9f7e84b    0
    
     Statement Statistics:
      Page       Buffer     Read       Buffer     Page       Buffer     Write
      Read       Read       % Cache    IDX Read   Write      Write      % Cache
      0          0          0.00       0          0          0          0.00
    
      Lock       Lock       LK Wait    Log        Num        Disk       Memory
      Requests   Waits      Time (S)   Space      Sorts      Sorts      Sorts
      0          0          0.0000     0.000 B    0          0          0
    
      Total      Total      Avg        Max        Avg        I/O Wait   Avg Rows
      Executions Time (S)   Time (S)   Time (S)   IO Wait    Time (S)   Per Sec
      1          0.0001     0.0001     0.0001     0.000000   0.000000   14865.4207
    
      Estimated  Estimated  Actual     SQL        ISAM       Isolation  SQL
      Cost       Rows       Rows       Error      Error      Level      Memory
      2          1          1          0          0          CR         11240​


    Below is the query plan.

    QUERY: (OPTIMIZATION TIMESTAMP: 03-18-2020 01:43:45)
    ------
    select current
    
    Estimated Cost: 1
    Estimated # of Rows Returned: 1
    
      1) informix.unnamed_tbl_0: COLLECTION SCAN
    
    
    Query statistics:
    -----------------
    
      Table map :
      ----------------------------
      Internal name     Table name
      ----------------------------
      t1                unnamed_tbl_0
    
      type     table  rows_prod  time
      -----------------------------------
      clscan   t1     1          00:00.00
    
    
    QUERY: (OPTIMIZATION TIMESTAMP: 03-18-2020 01:44:56)
    ------
    select current from sysdual
    
    Estimated Cost: 2
    Estimated # of Rows Returned: 1
    
      1) informix.sysdual: SEQUENTIAL SCAN
    
    
    Query statistics:
    -----------------
    
      Table map :
      ----------------------------
      Internal name     Table name
      ----------------------------
      t1                sysdual
    
      type     table  rows_prod  est_rows  rows_scan  time       est_cost
      -------------------------------------------------------------------
      scan     t1     1          1         1          00:00.00   2
    ​



    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------