bteq export issue

Tools
Enthusiast

bteq export issue

I was trying to export DDLs for the existing tables. I wrote a bteq script as follows. Although width is set to 20000, it exports partial row. I tried to increase width size to max 60000, still I have no luck. It exported same partial rows.

Can somebody point me what is wrong in this bteq?

-----------------------------

.logon
.set width 20000
.set titledashes off

.export report file=cm_crt_table.sql

select requesttext(title '') from DBC.TABLES where databasename='WRK_DB' and tablename like 'CM%';

.export reset

.logoff

.quit

-----------------------------

To work around this, I ran the SQL in SQL assistant to export it into excel sheet, where I got full length DDL text.

Tags (2)
4 REPLIES
Enthusiast

Re: bteq export issue

Note that the default format of TVM.RequestText is 'X(255)' but the column type is VARCHAR(12500). Try this...

select requesttext (format 'x(12500)') ....
Enthusiast

Re: bteq export issue

requesttext does not always contain the create table text. You should use show table commands.

Run bteq to generate the show table commands, then .run that file:

.logon
.set width 200
.set titledashes off
.set format off
.set rtitle ''

.export report file=cm_crt_table.run

select 'Show Table ||Trim(Databasename)||'.'||Trim(Tablename)||';' (title '')
from DBC.TABLES where databasename='WRK_DB' and tablename like 'CM%'
and tablekind = 'T';

.export reset
.Export report file=cm_crt_table.sql
.set width 400
.set titledashes off
.set format off
.set rtitle ''
.run file = cm_crt_table.run
.export reset
.logoff

.quit
Enthusiast

Re: bteq export issue

@Jim Chapman : Yes, Your solution worked. Why couldn't .width size handle? So, formatting X(255) suppress rest of the text?

I am wondering how does the SQL assistant manage to get full text?
Enthusiast

Re: bteq export issue

@Jimm Cool. Thanks for the nice bteq.

Pardon my ignorance on DD tables. I just wanted to get something quick. I thought requesttext was suitable to get table definition.