UDTF in Lua: Example 3

This example creates a function called shaper that dynamically shapes an output table. Instead of providing the names and data types of the output columns, this example calls, at runtime, the calculateShape function to dynamically determine which name and data type to assign to each output column.

Code

Put the following code into /tmp/udtf_example3.nzl:

-- Usage Example:
-- select * from table(shaper(2009,100,200,300,400))

function newInputRow(y,q1,q2,q3,q4)
        sls = {}
        sls[1] = { y, 1, q1 }
        sls[2] = { y, 2, q2 }
        sls[3] = { y, 3, q3 }
        sls[4] = { y, 4, q4 }
        return sls
end

function calculateShape(args)
        columns = {}
        columns[1] = { "Year", integer }
        columns[2] = { "Quarter", integer }
        columns[3] = { "Sales", double  }
        return columns
end

function getType()
        return udtf
end

function getName()
        return "shaper"
end

function getArgs()
    return {{ "year",     integer },
            { "q1sales",  double  },
            { "q2sales",  double  },
            { "q3sales",  double  },
            { "q4sales",  double  }}
end

function getShape()
    return ANYSHAPE
end

function getOptions()
    options = {}
    options[OPT_PARALLEL]  = false
    return options
end

Compression

Compress the file /tmp/udtf_example3.nzl into a file with the name /tmp/udtf_example3.tar.

Deployment

Deploy the UDX to a Db2® instance by issuing the following CLPPlus command:

SQL> IDA DEPLOYUDX SRC /tmp/udtf_example3.tar LANGUAGE lua PROJECT example3

Running

Use this function in a SELECT statement, for example:

SELECT * FROM TABLE(shaper(2009,100,200,300,400))

Year        Quarter     Sales
----------- ----------- ------------------------
       2009           1   +1.00000000000000E+002
       2009           2   +2.00000000000000E+002
       2009           3   +3.00000000000000E+002
       2009           4   +4.00000000000000E+002