Dynamic SQL

To dynamically create a table based on a query that might vary from run to run, compose the dynamic query using the CREATE TABLE AS command:

create or replace table qtemp/mytemptable as select * from whatever;

I'm assuming DB2 and added the DB2 Topic Area to pull in Experts that monitor that area.

I don't know DB2 and until they get here, it looks like you can do JSON and dynamic SQL in DB2.

I find temp tables are typically over used as a crutch.  It's almost always better to generate what you need directly with SQL whenever possible.  Most databases these days can generate JSON directly from the column values in a single select statement.  I ONLY use temp tables as a very last resort.

I found these links.  I hope they help:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajp/rzajpexampsqlirpg.htm
https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/json/src/tpc/db2z_jsonfunctions.html

Thanks both for the input.
I almost don't know anythin about (stored)procedures, can they help to make it less complex?

The wole story is; I receive a SQL command as parm. Have to run it, and send the response as Json.
Could be huge so probably as base64 envoded zip file.

Stored procedures can help with business logic that is really complex or just cannot be done with straight SQL.

I again state:  More often than not, if you can do something with straight SQL, best to do it with straight SQL.

I'm not sure what base64 encoding will help with.  Straight text files compress quite well.  What do you think encoding it will help solve?

Other than that, you haven't provided anything we can really help with.  There isn't any detail in what you've posted so we cannot really provide anything specific.

Hi,

As slightwv points out you should scope this functionality into a stored procedure. Thus the creation of a table from a dynamic select statement, select from that table and then let the procedure return the JSON object from that resultset.
Stored Procedure are good way to handle this kind of work and scope this into one single UOW.

As you are working with DB2 on iSeries then you need to be on 7.4 for the JSON functionality (I guess you already are :) )
See the following links how you could set this up.
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/sqlp/rbafycrttblas.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/sqlp/rbafysproeg.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/sqlp/rbafyresultsete1.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/sqlp/rbafyjson.htm

Regards,
     Tomas Helgi

>>As slightwv points out you should scope this functionality into a stored procedure.

I'm actually against stored procedures and temp tables.  They should ONLY be used as last resorts when absolutely nothing else works.

The only time I would go with a stored procedure first is when you need to mask or hide business logic from the application.  I suppose this question might warrant a procedure since it seems to deal with parameters but without more detail into exactly what is going on here, I cannot say.

Hi,

The only time I would go with a stored procedure first is when you need to mask or hide business logic from the application.  I suppose this question might warrant a procedure since it seems to deal with parameters but without more detail into exactly what is going on here, I cannot say.                                   

Stored procedure are essential not only hide logic and/or tables from application but also from hackers. They also prevent hackers to execute DML statements directly against tables if you only expose to certain connections/users and allow execution to stored procedures and user defined functions.
Exposing tables with sensitive data (which is very broad definition) to applications and/or database users/connections may be vulnerable to data breaches. It is all about how you secure your data. And SP/UDF can help with that as well as scope some logic into one place.

I almost don't know anythin about (stored)procedures, can they help to make it less complex? 

They may help in bringing some application logic closer to the data, thus relieving the application from that logic, as well as what I mention above.
You bring the logic that belongs to the data closer to it and thus make the logic in the application less complex.

Regards,
   Tomas Helgi