DDL without dbschema
A user asked how to extract table DDL from an Informix database without using dbschema, because he wanted pure-SQL scripts to build archecker command files (where the target-side DDL must drop constraints and defaults) for restores with archecker/onbar. Replies suggested Art Kagel's myschema (utils2_ak from the IIUG repository, which splits create-table DDL from other DDL), dbdiff2.4gl, or dbaccess's INFO TABLES; Art noted myschema.ec's source shows the catalog queries, and some parts can't be done in plain SQL. Another poster supplied SPL functions (SysTabId, SysColType, SysTabDef) that decode syscolumns coltype/collength into data types, which the original poster adapted to emit CREATE TABLE statements. His follow-up question about embedding newlines in an LVARCHAR went unanswered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good morining
Anybody know how to extract DDL of the tables in a database without dbschema.
I want to create script to restore all tables in a database using archecker
and onbar.
Thanks a lot
Regards
Servio Velasco
Why not use dbschema? For that matter, you can use my dbschema replacement
tool, myschema, which is in the package utils2_ak which you can download
from the IIUG Software Repository.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Jan 28, 2013 at 10:49 AM, SERVIO VELASCO <servio_velasco@hotmail.com
> wrote:
> Good morining
>
> Anybody know how to extract DDL of the tables in a database without
> dbschema.
>
> I want to create script to restore all tables in a database using archecker
> and onbar.
>
> Thanks a lot
> Regards
>
> Servio Velasco
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04428768b3863f04d45b8e3e
You can use Art's my schema or dbdiff2.4gl - compare to an empty =
database. Both available from the iiug site.
j.
On Jan 28, 2013, at 10:49 AM, SERVIO VELASCO wrote:
> Good morining=20
>=20
> Anybody know how to extract DDL of the tables in a database without =
dbschema.=20
>=20
> I want to create script to restore all tables in a database using =
archecker=20
> and onbar.=20
>=20
> Thanks a lot=20
> Regards=20
>=20
> Servio Velasco=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
You can use the dbaccess SQL command INFO TABLES
On 28 Jan 2013, at 15:49, SERVIO VELASCO <servio_velasco@hotmail.com> wrote:
> Good morining
>
> Anybody know how to extract DDL of the tables in a database without dbschema.
>
> I want to create script to restore all tables in a database using archecker
> and onbar.
>
> Thanks a lot
> Regards
>
> Servio Velasco
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Art
Good morning
The razon is:
I need restore all tables of a database using archecker and a backup in onbar.
Archecker use a commands file, in that commands file in part first define ddl
of the tables of restore and de part second define ddl the target tables, in
my case I need to eliminate the constraints clauses and default values in ddl
of part second.
I want to use sql language to make the scripts.
In the system catalog exist "systable", "syscolumns" but I need more
information to make ddl of all tables.
Anybody kow to if exists a table has name of data type.
Regards
Servio Velasco
Art,
Just a suggestion: Try adapt your myschema to work as function/datablade.
Something like the Oracle DBMS_METADATA package.
In certain situations it may be very useful.
Regards
Cesar
On 28/1/2013 14:13, Art Kagel wrote:
> Why not use dbschema? For that matter, you can use my dbschema replacement
> tool, myschema, which is in the package utils2_ak which you can download
> from the IIUG Software Repository.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Mon, Jan 28, 2013 at 10:49 AM, SERVIO VELASCO <servio_velasco@hotmail.com
>> wrote:
>> Good morining
>>
>> Anybody know how to extract DDL of the tables in a database without
>> dbschema.
>>
>> I want to create script to restore all tables in a database using archecker
>> and onbar.
>>
>> Thanks a lot
>> Regards
>>
>> Servio Velasco
>>
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
> --f46d04428768b3863f04d45b8e3e
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hello
I dug this from some archive (I used it on some older versions, maybe
you'll have to update it - if so please post your updated code here :))
Basically here are 2 functions and 1 procedure and you can use it e.g.
execute procedure SysTabDef('systables');HTH
Hrvoje
CREATE FUNCTION SysTabId(aTab CHAR(64)) RETURNS INTEGER;
RETURN NVL((SELECT tabid FROM systables WHERE UPPER(tabname) =
UPPER(aTab)),-1);
END FUNCTION;
CREATE PROCEDURE SysTabDef(aTab CHAR(64)) RETURNS CHAR(64),CHAR(64);
DEFINE pCol CHAR(64);
ON EXCEPTION END EXCEPTION WITH RESUME;
FOREACH SELECT colname INTO pCol FROM syscolumns WHERE tabid =
SysTabId(aTab) ORDER BY colno
RETURN pCol,SysColType(aTab,pCol) WITH RESUME;
END FOREACH;
END PROCEDURE;
CREATE FUNCTION SysColType(aTab CHAR(64), aCol CHAR(64)) RETURNS CHAR(255);
DEFINE cType CHAR(255);
DEFINE pColType,pColLength,pPrecision,pScale,pExtend INTEGER ;
DEFINE dtQualF,dtQualL CHAR( 12);
DEFINE dtQF ,dtQL INTEGER ;
LET pColType = NVL((SELECT coltype FROM syscolumns WHERE
UPPER(colname) = UPPER(aCol) AND tabid = SysTabId(aTab)),999999);
LET pColLength = NVL((SELECT collength FROM syscolumns WHERE
UPPER(colname) = UPPER(aCol) AND tabid = SysTabId(aTab)),999999);
LET pExtend = NVL((SELECT extended_id FROM syscolumns WHERE
UPPER(colname) = UPPER(aCol) AND tabid = SysTabId(aTab)),0 );
IF pColType <> 999999 THEN LET pColType = MOD(pColType,256);
END IF;
IF pColLength < 0 THEN LET pColLength = 65536-pColLength ;
END IF;
LET pScale = MOD(pColLength,256) ;
LET pPrecision = pColLength/256 ;
LET dtQL = MOD(pColLength,16) ;
LET dtQF = MOD(pColLength/16,16);
LET cType = DECODE(pColType,0,"CHAR" ,
1,"SMALLINT" ,
2,"INTEGER" ,
3,"FLOAT" ,
4,"SMALLFLOAT",
5,"DECIMAL" ,
6,"SERIAL" ,
7,"DATE" ,
8,"MONEY" ,
9,"NULL" ,
10,"DATETIME" ,
11,"BYTE" ,
12,"TEXT" ,
13,"VARCHAR" ,
14,"INTERVAL" ,
15,"NCHAR" ,
16,"NVARCHAR" ,
17,"INT8" ,
18,"SERIAL8" ,
19,"SET" ,
20,"MULTISET" ,
21,"LIST" ,
22,"ROW" ,
4118,"ROW" ,
"-");
IF pExtend <> 0 THEN LET ctYPE = NVL((SELECT UPPER(name) FROM
sysxtdtypes WHERE extended_id = pExtend),"-"); END IF;
IF pColType IN ( 0,15) THEN LET cType =
TRIM(cType)||"("||pColLength||")";
ELIF pColType IN ( 5 ,8) THEN LET cTYpe =
TRIM(cType)||"("||pPrecision||","||pScale ||")";
ELIF pColType IN (13,16) THEN LET cTYpe =
TRIM(cType)||"("||pScale ||","||pPrecision||")";
ELIF pColType IN (10,14) THEN -- = 10 THEN
LET dtQualF =
DECODE(dtQF,0,"YEAR",2,"MONTH",4,"DAY",6,"HOUR",8,"MINUTE",10,"SECOND",11,"FRACT
ION(1)",12,"FRACTION(1)",13,"FRACTION(2)",14,"FRACTION(3)",15,"FRACTION(4)","-")
;
LET dtQualL =
DECODE(dtQL,0,"YEAR",2,"MONTH",4,"DAY",6,"HOUR",8,"MINUTE",10,"SECOND",11,"FRACT
ION(1)",12,"FRACTION(1)",13,"FRACTION(2)",14,"FRACTION(3)",15,"FRACTION(4)","-")
;
IF pColType = 10 THEN
LET cType = TRIM(cType)||" "||TRIM(dtQualF)||" TO
"||TRIM(dtQualL);
ELSE
LET cType = TRIM(cType)||"
"||TRIM(dtQualF)||"("||pPrecision||") TO "||TRIM(dtQualL);
END IF;
END IF;
RETURN (cType);
END FUNCTION;
On 28.1.2013. 16:49, SERVIO VELASCO wrote:
> Good morining
>
> Anybody know how to extract DDL of the tables in a database without dbschema.
>
> I want to create script to restore all tables in a database using archecker
> and onbar.
>
> Thanks a lot
> Regards
>
> Servio Velasco
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
myschema -d somedatabase create.sql other_ddl.sql
Now the create.sql file contains only create table ddl and ddl for user
defined types. Everything else is written to the second file
(other_ddl.sql).
As far as what you need to know to write it yourself using SQL only, if you
look at the source for the mainline source file for myschema, myschema.ec,
all of the SQL queries and translation code is in there. Some of what you
want can't be easily programmed in SQL, you would have to write stored
procedures for it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Jan 29, 2013 at 8:49 AM, SERVIO VELASCO
<servio_velasco@hotmail.com>wrote:
> Art
>
> Good morning
>
> The razon is:
> I need restore all tables of a database using archecker and a backup in
> onbar.
>
> Archecker use a commands file, in that commands file in part first define
> ddl
> of the tables of restore and de part second define ddl the target tables,
> in
> my case I need to eliminate the constraints clauses and default values in
> ddl
> of part second.
>
> I want to use sql language to make the scripts.
>
> In the system catalog exist "systable", "syscolumns" but I need more
> information to make ddl of all tables.
>
> Anybody kow to if exists a table has name of data type.
>
> Regards
>
> Servio Velasco
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e0cb4efe33a805146104d46e98d9
I'll take it under advisement. What does the Oracle package do? Don't
know Oracle internals.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Jan 29, 2013 at 8:56 AM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Art,
>
> Just a suggestion: Try adapt your myschema to work as function/datablade.
> Something like the Oracle DBMS_METADATA package.
>
> In certain situations it may be very useful.
>
> Regards
> Cesar
>
>
> On 28/1/2013 14:13, Art Kagel wrote:
>
> Why not use dbschema? For that matter, you can use my dbschema replacement
> tool, myschema, which is in the package utils2_ak which you can download
> from the IIUG Software Repository.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Mon, Jan 28, 2013 at 10:49 AM, SERVIO VELASCO <servio_velasco@hotmail.com
>
> wrote:
>
> Good morining
>
> Anybody know how to extract DDL of the tables in a database without
> dbschema.
>
> I want to create script to restore all tables in a database using archecker
> and onbar.
>
> Thanks a lot
> Regards
>
> Servio Velasco
>
>
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> --f46d04428768b3863f04d45b8e3e
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
--e0cb4efe33a82a56a104d46ea0b5
Hello
Thanks for yours procedures.
I modified SysTabDef but I have a detail. Do you know how to add a newline in
LVARCHAR variable? Because output look best.
CREATE PROCEDURE SysTabDef(aTab CHAR(64)) RETURNS LVARCHAR(1000);DEFINE pCol CHAR(64);
Define sDDL lvarchar(1000);
define iFirst integer;
ON EXCEPTION END EXCEPTION WITH RESUME;
let sDDL = '' || trim(aTab) ||'(' ;
let iFirst = 0;
FOREACH SELECT colname INTO pCol FROM syscolumns WHERE tabid =
SysTabId(aTab) ORDER BY colno
if (iFirst = 0) then
let sDDL = sDDL || trim(pCol) || ' ' || trim(SysColType(aTab,pCol));
let iFirst = 1;
else
let sDDL = sDDL || ',' || trim(pCol) || ' ' || trim(SysColType(aTab,pCol));
end if
END FOREACH;
let sDDL = sDDL || ");";
return sDDL;
END PROCEDURE;
select "create table " || SysTabDef('sitf_trazacartaconfirmacion') || " in
datadbs;" from sysmaster:sysdual ;
Regards
Servio Velasco
It work's as regular function , where return a CHAR field with the DDL
of the object :
select dbms_metadata.get_ddl('VIEW','someviewname') from dual;
they will return the CREATE VIEW someviewname.....
On 29/1/2013 12:58, Art Kagel wrote:
> I'll take it under advisement. What does the Oracle package do? Don't
> know Oracle internals.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Tue, Jan 29, 2013 at 8:56 AM, Cesar Inacio Martins <
> cesar_inacio_martins@yahoo.com.br> wrote:
>
>> Art,
>>
>> Just a suggestion: Try adapt your myschema to work as function/datablade.
>> Something like the Oracle DBMS_METADATA package.
>>
>> In certain situations it may be very useful.
>>
>> Regards
>> Cesar
>>
>>
>> On 28/1/2013 14:13, Art Kagel wrote:
>>
>> Why not use dbschema? For that matter, you can use my dbschema replacement
>> tool, myschema, which is in the package utils2_ak which you can download
>> from the IIUG Software Repository.
>>
>> Art
>>
>> Art S. Kagel
>> Advanced DataTools (www.advancedatatools.com)
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
>> other organization with which I am associated either explicitly,
>> implicitly, or by inference. Neither do those opinions reflect those of
>> other individuals affiliated with any entity with which I am affiliated nor
>> those of the entities themselves.
>>
>> On Mon, Jan 28, 2013 at 10:49 AM, SERVIO VELASCO <servio_velasco@hotmail.com
>>
>> wrote:
>>
>> Good morining
>>
>> Anybody know how to extract DDL of the tables in a database without
>> dbschema.
>>
>> I want to create script to restore all tables in a database using archecker
>> and onbar.
>>
>> Thanks a lot
>> Regards
>>
>> Servio Velasco
>>
>>
>>
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>> --f46d04428768b3863f04d45b8e3e
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>>
> --e0cb4efe33a82a56a104d46ea0b5
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>