Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power

 View Only

  • 1.  Built-in Function Selection

    Posted Thu March 28, 2024 04:15 PM

    A Db2 implementation detail to consider before choosing a function to use.

    https://db2ibmi.blogspot.com/2024/03/not-all-db2-functions-are-created-equal.html



    ------------------------------
    Kent Milligan
    IBM Technology Expert Labs
    ------------------------------

    #SQL


  • 2.  RE: Built-in Function Selection

    Posted Fri April 05, 2024 10:41 AM
    CREATE INDEX for Numeric Date and Time Columns
    Thanks Kent. SQL0356 is a bit obscure.
    CREATE INDEX testtime3 ON testtime (TIMESTAMP(lmdt||LPAD(lmtm,6,0),0));


    ------------------------------
    John Gojnich
    ------------------------------

    Original Message


  • 3.  RE: Built-in Function Selection

    Posted Sat April 06, 2024 01:33 AM

    This will not answer your question, but may be solve your problem, i.e. create an index

    Why you want to use LPAD when creating an Index over a numeric date and time?

    I assume the numeric date is defined as DEC(8, 0) and includes a numeric date in the format YYYYMMDD and the time is defined as DEC(6, 0) and include a time in the format HHMMSS.

    In this case you could create an index as follows:

    Create Index YourSchema.YourIndex
        on YourSchema.Yourtable
             (timestamp(digits(NumDate) concat digits(Numtime)) as TS);

    Just in case your time is only 4 digit in the format HHMM you could create an index as follows:

    Create Index yourschema.yourindex
        on yourschema.yourtable
            (timestamp(digits(NumDate) concat digits(NumTime4) concat '00') as TS);

    ... but keep in mind the index can only be used if you use the same syntax (for the timestamp) in your queries.

    The easiest way would be to generate a view with an additional column where the timestamp is prepared in the same way as in your index (or vice versa).



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


  • 4.  RE: Built-in Function Selection

    Posted Sat April 06, 2024 02:32 AM

    It was an example from an SQL Plan Cache, that got me thinking about numeric date and time fields from legacy systems. So I posted a reminder to myself that TIMESTAMP(lmdt||DIGITS(lmtm),0) works. 

    I think a lot of confusion stems from the opposite (missing) function DIGITS(current_date -1 DAY)

    with yesterday (option1, option2) as (
            values(to_char(current_date -1 day,'YYYYMMDD'), replace(char(current_date -1 day,ISO),'-','')))
    -- why not digits(current_date -1 day)?
    select * from yesterday; 



    ------------------------------
    John Gojnich
    ------------------------------

    Original Message


Related Content