Creating new table with same structure as existing one

[Nebojsa Sevo]

I need to create "archive" table that will have the same structure as
"production" one.

When I run my program it have to create table named "pronop_yyyymm" ( yyyymm is
argument to my program representing date in format year(4)month(2)) if it
doesn't exist. I will write rows from table "pronop" that are in given
month/year to "pronop_yyyymm" and delete them from "pronop" (I know that DELETE
is very slow but I don't have alternative).
What I want is not to change program (or part of program where I create
"pronop_yyyymm") when structure of table "pronop" is changed.
I am using I4GL. I have an idea to do it with "dbschema" and some scripting.

Is there any other way?

nebojsa
------------------------------------
Remove spam block (DELETE_) to reply

[mosserp@wellsfargo.com]

How about altering the table to fragment it, putting the yyyymm rows into a
separate fragment, and then detaching that fragment into the new table? The
effectiveness of this approach probably depends on how big the original
table is.

HTH,
Paul Mosser

-----Original Message-----
From: Nebojsa Sevo [mailto:DELETE_mips@zg.tel.hr]
Sent: Tuesday, October 14, 2003 2:59 AM
To: [email]informix-list@iiug.org[/email]
Subject: Creating new table with same structure as existing one


I need to create "archive" table that will have the same structure as
"production" one.

When I run my program it have to create table named "pronop_yyyymm" ( yyyymm
is
argument to my program representing date in format year(4)month(2)) if it
doesn't exist. I will write rows from table "pronop" that are in given
month/year to "pronop_yyyymm" and delete them from "pronop" (I know that
DELETE
is very slow but I don't have alternative).
What I want is not to change program (or part of program where I create
"pronop_yyyymm") when structure of table "pronop" is changed.
I am using I4GL. I have an idea to do it with "dbschema" and some scripting.

Is there any other way?

nebojsa
------------------------------------
Remove spam block (DELETE_) to reply
sending to informix-list

[preetinder dhaliwal]

I have a small ec program, which will return the create table statement.

It is written for data types mentioned in array . Call this from
4gl with params (tabname,length(tabname)) returning l_create_stmt.

This is bit crude, but u can refine it.



================================================== ===========
#include<stdlib.h>

#include<stdio.h>

#include<sqltypes.h>

#include<ifxtypes.h>

#include<math.h>

#include<datetime.h>



char *myvals[24] = { " char\("," smallint"," int","",""," decimal\(","
serial","
date","",""," datetime","","","
varchar\(","","","","","","","","","",""};
char *dtimes[16] =
{"YEAR","","MONTH","","DAY","","HOUR","","MINUTE", "","SECOND"
,"fraction\(1\)","fraction\(2\)","fraction\(3\)"," fraction\(4\)","fraction\(5\)"
};

int tabsel(int
nargs)
{

exec sql begin declare
section;
char
l_colname[50];
int
l_coltype;
int
l_colno;
int
l_collength;
char
l_mystmt[300];
exec sql end declare
section;
int
tmp;
int
tmp1;
char
tabname[50];
int
len;
int
i;
char
l_stmt[300];
char
selstmt[1000];

popint(&len);
popquote(tabname,(len + 1
));
sprintf(l_mystmt,"select trim(colname),coltype,colno,collength
from sys
columns s,systables t where s.tabid=t.tabid and t.tabname= \"%s\" order
by colno
",tabname);

/*exec sql database
euro50k;*/
exec sql prepare prep_my from :l_mystmt
;
if (sqlca.sqlcode != 0
)

{

retquote("");

retint(sqlca.sqlcode);

return(2);

}
exec sql declare d_cur cursor for
prep_my;
if (sqlca.sqlcode != 0
)

{

retquote("");

retint(sqlca.sqlcode);

return(2);

}
exec sql open
d_cur;
if (sqlca.sqlcode != 0
)

{

retquote("");

retint(sqlca.sqlcode);

return(2);

}
sprintf(selstmt,"create table %s
\(",tabname);
while (sqlca.sqlcode ==
0){


exec sql fetch next d_cur into
:l_colname,:l_coltype,:l_colno,:l
_collength;

if ( sqlca.sqlcode == 100
)

break;


while ( l_coltype >= 256
){
l_coltype = l_coltype -
256;

}
if ( (2<l_coltype && l_coltype <5) || (7<l_coltype &&
l_coltype<
10) || (10< l_coltype && l_coltype<12 ) || (l_coltype >13)
){

retquote("");

retint(2);

return(2);

}



for(i=0;i<strlen(l_colname);i++)

{
if ( l_colname[i]=='
')

{

l_colname[i]='\0';

break;

}

}

strcat(selstmt,l_colname);

strcat(selstmt,myvals[l_coltype]);


switch
(l_coltype){
case 0:
sprintf(l_stmt,"%d\)\,",l_collength);

break;
case 1:
sprintf(l_stmt,"\,");

break;
case 2:
sprintf(l_stmt,"\,");

break;
case 5:
tmp=l_collength/256;
tmp1= l_collength -
tmp*256;

sprintf(l_stmt,"%d\,%d\)\,",tmp,tmp1);

break;
case 6:
sprintf(l_stmt,"\,");

break;
case 7:
sprintf(l_stmt,"\,");

break;
case 10: sprintf(l_stmt," %s to %s
\,",dtimes[TU_START(l
_collength)],dtimes[TU_END(l_collength)]);


break;
case 13:
tmp=l_collength/256;

tmp1=remainder(l_collength,256);

sprintf(l_stmt,"%d\,%d\)\,",tmp1,tmp);

break;

}

strcat(selstmt,l_stmt);

}
selstmt[strlen(selstmt) - 1] = ')';
retquote(selstmt);
retint(0);
return(2);
}
================================================== ========

Hope this helps

Rgds
Preetinder


Nebojsa Sevo wrote:

>I need to create "archive" table that will have the same structure as
>"production" one.
>
>When I run my program it have to create table named "pronop_yyyymm" ( yyyymm is
>argument to my program representing date in format year(4)month(2)) if it
>doesn't exist. I will write rows from table "pronop" that are in given
>month/year to "pronop_yyyymm" and delete them from "pronop" (I know that DELETE
>is very slow but I don't have alternative).
>What I want is not to change program (or part of program where I create
>"pronop_yyyymm") when structure of table "pronop" is changed.
>I am using I4GL. I have an idea to do it with "dbschema" and some scripting.
>
>Is there any other way?
>
>nebojsa
>------------------------------------
>Remove spam block (DELETE_) to reply
>
>
>

sending to informix-list

[Art S. Kagel]

On Tue, 14 Oct 2003 05:59:27 -0400, Nebojsa Sevo wrote:

Actually you do have an alternative. Create a second fragment of the table
containing the rows you want to archive using ALTER FRAGMENT ON TABLE pronop
INIT FRAGMENT BY EXPRESSION (datecolumn < "2002-02" IN dbspace_1, REMAINDER IN
current_dbspace;. Then detach that fragment to an independent table using ALTER
FRAGMENT ON TABLE pronop DETACH dbspace_1 pronop_200202.


Art S. Kagel

> I need to create "archive" table that will have the same structure as
> "production" one.
>
> When I run my program it have to create table named "pronop_yyyymm" ( yyyymm
> is argument to my program representing date in format year(4)month(2)) if it
> doesn't exist. I will write rows from table "pronop" that are in given
> month/year to "pronop_yyyymm" and delete them from "pronop" (I know that
> DELETE is very slow but I don't have alternative). What I want is not to
> change program (or part of program where I create "pronop_yyyymm") when
> structure of table "pronop" is changed. I am using I4GL. I have an idea to do
> it with "dbschema" and some scripting.
>
> Is there any other way?
>
> nebojsa
> ------------------------------------
> Remove spam block (DELETE_) to reply