I am supposed to dump data from views into a file using TPT via UNIX scripts. I do not have access to create tables on the server. My requirement is to write column names, length and datatype into metadata file using UNIX script.
Since dbc catalogue does not store column length and datatype for views, there seems to be no better way to export the schema information.
If I had access to create table, I would have created a dummy table with no data and used DBC to get this info.
Tried creating a volatile table but this too does not write to DBC.
The below piece helps but returns too many fields which I am not interested in.
HELP COLUMN ECROEXT.CHECK_DAY_LU.*
It would have been really good if there was a way to be able to define an outer query over the above result where I could have filtered and manipulated what I needed.
I can put the above peice in BTEQ and export which to me seems as the only option but I feel embarassed thinking there may be a better way of doing this.
Experts, please help
Release 16.0 seems to provide this info via new dbc views.
Before that you have the choice
to use HELP COLUMN ECROEXT.CHECK_DAY_LU.* as long as you don't have the right to create tables
or you create a vt table with at least one row and generate and execute the
select type(colA), type(colB), ... from vt
there's no need for a row in a table when you switch to:
create volatile table vt as (select * from ECROEXT.CHECK_DAY_LU) with no data; select type(vt.colA), type(vt.colB), ...
create volatile table vt_txt2 as (select '10+(0.5 * 10)' as txt ) with no data no primary index on commit preserve rows; select type(txt) from vt_txt2;
where when I run this "with data" and one row I get infos...
So what I am missing :)?
Don't use FROM:
CREATE VOLATILE TABLE vt_txt2 AS (SELECT '10+(0.5 * 10)' AS txt ) WITH NO DATA; SELECT TYPE( vt_txt2.txt); Type(txt) VARCHAR(13) CHARACTER SET UNICODE