Get column width of fields in views

Database

Get column width of fields in views

Hi,

 

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

 

Thanks

Tags (1)
4 REPLIES
Supporter

Re: Get column width of fields in views

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

 

Ulrich

 

Junior Contributor

Re: Get column width of fields in views

Hi Ulrich,

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), ... 

 

Supporter

Re: Get column width of fields in views

I tried:

 

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;

and got

 

Type(txt)

where when I run this "with data" and one row I get infos...

So what I am missing :)?

Tags (1)
  • Tags:
Junior Contributor

Re: Get column width of fields in views

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

:-)