For easier "alter table" in our Company I've developed this ABF/4GL-program that handles
Issues for a number tables that needs to be changed since last run in the correct order.
It takes it's input from a table called b_dbchg with a column indicating new, chg or delete
And creates an SQL-script for each table that needs changes (sps_skriv is simply a 4GL-procedure
That calls relevant file-functions open file for write, writeln and close etc.
The 4GL-procedure is called from a Shell-script that afterwards runs the generated SQL-scripts.
And also re-creates Views, Database-procedures and Rules etc if needed - especially with changes
Of datatypes for Views.
Please also note "some" other handling issues implemented with "modify" to heap and dropping indexes
Etc which is caused by the need to handle changes for key-columns, and to implement in-between adding
Of several columns.
The table has as example
New column
Lbnr=1 Type='n' Table='mytable' Feltnavn='mynewcolumn' Datatype='integer not null with default'
Lbnr=2 Type='n' Table='mytable' Feltnavn='mywrongcolumn' Datatype='varchar(20) with null'
Lbnr=3 Type='c' Table='mytable' Feltnavn='mynewcolumn' Datatype='decimal(15, 0) not null with default'
Lbnr=4 Type='d' Table='mytable' Feltnavn='mywrongcolumn' Datatype='-'
Meaning First Run Creates the columns mynewcolumn and mywrongcolumn.
Second Run changes datatype on mynewcolumn and deletes mywrongcolumn.
This principle was part of my presentation at the IUA in London June 2009 "Easy DBA-work', as I handles
Above 100 identical production and test databases inside our company.
Kind regards
Henrik Georg Sørensen, Denmark
Part of text Is first 4GL-procedure followed by Shell-script. Sorry for some Danish Words in Code.
/*
** PROGRAMNAVN: chg_database
** TYPE.......: PROCEDURE
** KILDETEKST.: chg_database.osq
** AF.........: HGS 19.02.2008
** FORMÅL.....: Auto Change Database Jobs
** PARAMETRE..:
** RETUR......:
**
** TABELLER...: SELECT - UPDATE - INSERT - DELETE:
**
** KALDER.....:
** INCLUDES...:
*/
#include <def/define.h>;
#include <sps/konstant.h>;
procedure chg_database() =
declare
parametre = varchar(32) not null,
tabelnavn = varchar(32) not null,
indeksnavn = varchar(32) not null,
katalog = varchar(250) not null,
filnavn = varchar(250) not null,
chg_tabeljob = procedure returning integer not null;
begin
g_svar = konv_setup();
if g_svar != OK then
return :g_svar;
endif;
parametre = CommandLineParameters();
katalog = sps_path(ident = 'optimize');
katalog = katalog + 'chgsql/' + g_database + '/';
message varchar(date('now')) + ' Parametre : ' + parametre;
if parametre = '' then
select tabelnavn = i.table_name
from iitables i
where i.table_owner = dbmsinfo('dba') and i.table_type = 'T' and
i.expire_date = 0 and left(i.table_name, 6) != 'iietab'
order by tabelnavn
begin
g_dummy = chg_tabeljob(tabelnavn = tabelnavn);
end;
else
select tabelnavn = i.table_name
from iitables i
where i.table_owner = dbmsinfo('dba') and i.table_type = 'T' and
squeeze(varchar(i.table_name)) like :parametre escape '\' and
i.expire_date = 0 and left(i.table_name, 6) != 'iietab'
order by tabelnavn
begin
g_dummy = chg_tabeljob(tabelnavn = tabelnavn);
end;
endif;
commit;
end
procedure chg_tabeljob(tabelnavn = varchar(32) not null) = declare
aendret_jn = varchar(3),
chg_jn = varchar(3),
chg_type = varchar(32) not null,
size_jn = varchar(3),
page_size = integer not null,
lbnr = integer not null,
org_datatype = varchar(50) not null,
datatype = varchar(50) not null,
column_sequence = integer not null,
column_name = varchar(32) not null,
column_datatype = varchar(32) not null,
column_length = integer not null,
column_scale = integer not null,
column_nulls = varchar(1) not null,
column_defaults = varchar(1) not null,
sql_tekst = varchar(200) not null,
begin
chg_jn = NEJ;
if left(tabelnavn, 2) = 'cx' then
return OK;
elseif locate('xyz', left(tabelnavn, 1)) <= 3 then
return OK;
endif;
message varchar(date('now')) + ' Behandler : ' + tabelnavn;
filnavn = katalog + tabelnavn + '.sql';
g_dummy = sps_skriv(type = 'w', tekst = :filnavn);
g_dummy = sps_skriv(type = 'g', tekst = 'set norules;');
g_dummy = sps_skriv(type = 'g', tekst = 'set autocommit on;\p\g');
/* Drop alle Index af hensyn til evt Key herfor */
select indeksnavn = index_name
from iiindexes
where base_name = :tabelnavn and base_owner = index_owner and
base_owner = dbmsinfo('dba')
order by indeksnavn
begin
if left(indeksnavn, 1) = '$' then
indeksnavn = shift(indeksnavn, -1);
sql_tekst = 'alter table ' + :tabelnavn
+ ' drop constraint ' + :indeksnavn + ' cascade;\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
else
g_dummy = sps_skriv(type = 'g',
tekst = 'drop index ' + :indeksnavn + ';\p\g');
endif;
end;
/* Oprindelig Page.Size */
repeated
select page_size = table_pagesize
from iitables
where table_owner = dbmsinfo('dba') and table_type = 'T' and
table_name = :tabelnavn;
commit;
if page_size != 8192 then
/* Skift page.size til 8192 ifbm ændring */
sql_tekst = 'modify ' + :tabelnavn + ' to reconstruct'
+ ' with page_size = 8192;\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
endif;
/* Key columns kan ikke ændres så struktur til HEAP */
sql_tekst = 'modify ' + :tabelnavn + ' to heap;\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
/* Ændring datatype af eksisterende felter */
select column_sequence,
column_name,
column_datatype,
column_length,
column_scale,
column_nulls,
column_defaults
from iicolumns c
where table_owner = dbmsinfo('dba') and table_name = :tabelnavn
order by column_sequence
begin
aendret_jn = NEJ; chg_type = '';
if ( column_name = 'init'
or column_name = 'init_sagsbeh'
or column_name = 'sagsbeh'
or column_name = 'att'
or column_name = 'oprettet'
or column_name = 'endr_brg'
or right(column_name, 5) = '_init'
or column_name = 'bruger') and column_length <= 11 then
chg_jn = JA; aendret_jn = JA; chg_type = 'varchar(12)';
elseif column_name = 'avt_dba' and column_length <= 31 then
chg_jn = JA; aendret_jn = JA; chg_type = 'varchar(32)';
elseif ( right(column_name, 5) = 'kreds'
or right(column_name, 7) = 'kredsnr'
or left(column_name, 6) = 'region'
or left(column_name, 5) = 'kreds'
or left(column_name, 5) = 'afdnr'
or left(column_name, 6) = 'projnr'
or right(column_name, 6) = 'elemnr'
or left(column_name, 6) = 'kontkd'
or right(column_name, 6) = 'kontkd'
or left(column_name, 6) = 'klient'
or right(column_name, 6) = 'klient') and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif ( column_name = 'ktg_type'
or column_name = 'ktg_forsik'
or column_name = 'ktg_tlg'
or column_name = 'ryk_ktg_type'
or right(column_name, 4) = '_ktg'
or column_name = 'lbnr'
or right(column_name, 5) = '_lbnr'
or right(column_name, 4) = '_lin'
or column_name = 'feper_nr'
or column_name = 'beregn_nr'
or column_name = 'erkl_nr') and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif ( column_name = 'sats'
or column_name = 'beloeb'
or column_name = 'skat'
or column_name = 'atp'
or column_name = 'atp_pens'
or column_name = 'arbgiv_beloeb'
or right(column_name, 5) = 'timer') and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif ( column_name = 'art'
or right(column_name, 4) = '_art'
or column_name = 'gruppe'
or column_name = 'kode'
or right(column_name, 5) = '_kode'
or column_name = 'konto_ref'
or column_name = 'mlvnr'
or column_name = 'sumnr'
or right(column_name, 3) = '_nr'
or column_name = 'type'
or right(column_name, 5) = '_type'
or column_name = 'tpnr'
or right(column_name, 3) = '_tp') and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif ( locate(column_name, 'antal') <= length(column_name)
or locate(column_name, 'aarsag') <= length(column_name)) and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif ( column_name = 'ansvar'
or column_name = 'koder'
or column_name = 'fra_til_dag'
or column_name = 'version'
or right(column_name, 3) = '_kd'
or right(column_name, 3) = '_pr'
or right(column_name, 4) = '_afd'
or right(column_name, 4) = '_arbtid'
or right(column_name, 5) = '_dage'
or right(column_name, 5) = '_uger') and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif (right(column_name, 5) = 'regnr') and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif locate(column_name, 'postnr') <= length(column_name) and
column_datatype = 'INTEGER' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif column_datatype = 'TEXT' then
chg_jn = JA; aendret_jn = JA;
chg_type = 'varchar(' + varchar(column_length) + ')';
elseif column_name = 'roert' and
column_datatype = 'INTEGER' and column_length <= 2 then
if locate('bdefghijklnmorstu', left(tabelnavn, 1)) <= 17
or locate('st', charextract(tabelnavn, 2)) <= 2
or (g_dagsdato >= date('2008_12_12') and left(tabelnavn, 1) = 'p')
or g_dagsdato >= date('2009_01_01') then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
endif;
elseif tabelnavn = 'a_sup5278' and
column_name = 'ant_par60_uger' and column_length <= 2 then
chg_jn = JA; aendret_jn = JA; chg_type = 'integer';
elseif tabelnavn = 'gs_idtxt' and
column_name = 'id' and column_length < 20 then
chg_jn = JA; aendret_jn = JA; chg_type = 'varchar(20)';
elseif tabelnavn = 'gs_institution' and
column_name = 'extern_ref' and column_length < 36 then
chg_jn = JA; aendret_jn = JA; chg_type = 'varchar(36)';
endif;
if chg_type != '' then
sql_tekst = 'alter table ' + :tabelnavn
+ ' alter column ' + :column_name + ' ' + :chg_type;
if column_nulls = 'N' then
sql_tekst = sql_tekst + ' not null';
if column_defaults = 'Y' then
sql_tekst = sql_tekst + ' with default';
endif;
else
sql_tekst = sql_tekst + ' with null';
endif;
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst + ';\p\g');
message :sql_tekst;
else
repeated
select lbnr = b.lbnr,
datatype = b.datatype
from b_dbchg b
where b.type = 'c' and b.tabelnavn = :tabelnavn and
b.feltnavn = :column_name
order by lbnr desc;
inquire_sql(ing_antrk = rowcount, ing_fejl = errorno);
if ing_fejl = OK and ing_antrk >= 1 then
org_datatype = lowercase(column_datatype);
if org_datatype = 'integer' then
if column_length = 1 then
org_datatype = 'i1';
elseif column_length = 2 then
org_datatype = 'smallint';
elseif column_length = 4 then
org_datatype = 'integer';
elseif column_length = 8 then
org_datatype = 'longint';
endif;
if column_nulls = 'N' then
org_datatype = org_datatype + ' not null';
if column_defaults = 'Y' then
org_datatype = org_datatype + ' with default';
endif;
else
org_datatype = org_datatype + ' with null';
endif;
elseif org_datatype = 'varchar' or org_datatype = 'char' then
org_datatype = org_datatype + '(' + varchar(column_length) + ')';
if column_nulls = 'N' then
org_datatype = org_datatype + ' not null';
if column_defaults = 'Y' then
org_datatype = org_datatype + ' with default';
endif;
else
org_datatype = org_datatype + ' with null';
endif;
else
/* Undlad ændringer - ikke implementeret pt. */
org_datatype = datatype;
endif;
if datatype != org_datatype then
msg_tekst = 'Org: ' + org_datatype + '/'
+ 'Chg: ' + datatype;
message :msg_tekst;
sql_tekst = 'alter table ' + :tabelnavn
+ ' alter column ' + :column_name + ' ' + :datatype;
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst + ';\p\g');
chg_jn = JA; aendret_jn = JA;
message :sql_tekst;
endif;
endif;
endif;
if aendret_jn = JA then
/* Pga ellers kun en kolonne pr. script er i forvejen HEAP */
sql_tekst = 'modify ' + :tabelnavn + ' to heap;\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
endif;
end;
/* Tilføjelse af nye felter generelt */
/* Feks. ansidato ansidate not null with default,
** ingresdato ingresdate not null with default,
** sidststamp timestamp not null with default
**
** opret_tidspkt, sidst_tidspkt : timestamp
*/
select lbnr = b.lbnr,
column_name = b.feltnavn,
datatype = b.datatype
from b_dbchg b
where b.type = 'n' and b.tabelnavn = :tabelnavn and
not exists (select *
from iicolumns i
where i.table_owner = dbmsinfo('dba') and
i.table_name = b.tabelnavn and
i.table_name = :tabelnavn and
i.column_name = b.feltnavn)
order by lbnr
begin
chg_jn = JA;
sql_tekst = 'alter table ' + :tabelnavn
+ ' add column ' + :column_name + ' ' + :datatype;
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst + ';\p\g');
message :sql_tekst;
sql_tekst = 'modify ' + :tabelnavn + ' to heap;\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
end;
select lbnr = b.lbnr,
column_name = b.feltnavn,
datatype = b.datatype
from b_dbchg b
where b.type = 'd' and b.tabelnavn = :tabelnavn and
exists (select *
from iicolumns i
where i.table_owner = dbmsinfo('dba') and
i.table_name = b.tabelnavn and
i.table_name = :tabelnavn and
i.column_name = b.feltnavn)
order by lbnr
begin
chg_jn = JA;
sql_tekst = 'alter table ' + :tabelnavn
+ ' drop column ' + :column_name + ' cascade';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst + ';\p\g');
message :sql_tekst;
sql_tekst = 'modify ' + :tabelnavn + ' to heap;\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
end;
if page_size != 8192 then
/* Skift page.size til oprindelige efter ændring */
sql_tekst = 'modify ' + :tabelnavn + ' to reconstruct'
+ ' with page_size = ' + varchar(page_size) + ';\p\g';
g_dummy = sps_skriv(type = 'g', tekst = sql_tekst);
endif;
g_dummy = sps_skriv(type = 'c');
if chg_jn = NEJ then
/* Fjern filen igen
*/
g_dummy = sps_skriv(type = 'd', tekst = :filnavn);
endif;
end
Here comes the SHELL-script
#!/bin/ksh
# Program.: optimchg.sh <database> (<tabelnavn>)
# Af......: HGS 19.02.2008
# Formål..: Auto-ajourføring af tabeller mht felter # Kategori: Dba
if [ -r "${ENV}" ]; then
. ${ENV}
fi;
tabel='alle';
if [ $# -ne 1 -a $# -ne 2 ]; then
echo 'Kaldes: optimchg.sh <database> (<tabelnavn>)';
exit 1;
elif [ $# -eq 2 ]; then
tabel=${2};
fi
database=${1};
if [ ${database} = 'alle' ]; then
for database in `dblist.sh`; do
echo 'Database: '${database}
optimchg.sh ${database} ${tabel}
done;
exit 0
fi
dato=`date +"%Y%m%d"`
logfil=~forb/optimize/logger/${database}.optimchg.log
cd ~forb/optimize
if [ -r ./chgsql ]; then
echo 'OK' > /dev/null
else
mkdir chgsql
fi
cd ./chgsql
if [ -r ${database} ]; then
echo 'OK' > /dev/null
else
mkdir ./${database}
fi
cd ./${database}
rm -f *.sql 2> /dev/null
# Indlæs oplysning om felter til ændring
fiedist.sh ${database} dbchg | tee -a ${logfil}
kundedir=`kundeexe.sh database ${database}`
konving=~forb/${kundedir}/exe/konvert_ing.exe
fiesetup database ${database}
ORGANIBASE=${ORGANIBASE:=${database}};
if [ ${tabel} = 'alle' ]; then
${konving} -noforms -d${ORGANIBASE} chg_database
else
${konving} -noforms -d${ORGANIBASE} chg_database -a ${tabel}
Fi
fundet_jn='nej';
for fil in `ls *.sql 2> /dev/null`; do
tabelnavn=`echo ${fil} | cut -f1 -d"."`
cat <<+ | $II_SYSTEM/ingres/bin/sql -s ${ORGANIBASE} | tee -a ${logfil}
set norules;
set lockmode session where readlock = nolock, level = table;
set autocommit on;\g
select count(*) from ${tabelnavn};
\p\g\time
+
$II_SYSTEM/ingres/bin/copydb -c ${ORGANIBASE} -with_modify -with_index ${tabelnavn}
mv copy.in ${tabelnavn}.tmp
rm -f copy.out 2> /dev/null
cat -s ${tabelnavn}.tmp >> ${fil}
sql ${ORGANIBASE} < ${fil} | tee -a ${logfil}
cat <<+ | $II_SYSTEM/ingres/bin/sql -s ${ORGANIBASE} | tee -a ${logfil}
set norules;
set lockmode session where readlock = nolock, level = table;
set autocommit on;\g
select count(*) from ${tabelnavn};
\p\g\time
+
rm -f ${tabelnavn}.tmp 2> /dev/null
fundet_jn='ja';
if [ -r /tmp/$database.optimchg.stop ]; then
exit 0;
fi
done
if [ ${tabel} = 'alle' ]; then
cat <<+ | $II_SYSTEM/ingres/bin/sql -s ${ORGANIBASE} | tee -a ${logfil}
set norules;
set lockmode session where readlock = nolock, level = table;
set autocommit on;\g
declare global temporary table session.bt_dbchg
as select tidspkt = max(tidspkt)
from b_dbchg
on commit preserve rows with norecovery;
update b_master b
from session.bt_dbchg t
set data = varchar(t.tidspkt)
where b.modul = 'b' and b.navn = 'dbinfo';
\p\g\time
+
fi
if [[ ${database} != *konv ]]; then
optimheap.sh ${database}
if [ ${tabel} = 'alle' ]; then
optimindx.sh ${database} alle
fi
fi
if [ ${fundet_jn} = 'ja' -a ${tabel} = 'alle' ]; then
cd ~forb/sql/views
for fil in `ls *.sql 2> /dev/null`; do
$II_SYSTEM/ingres/bin/sql -s ${ORGANIBASE} < $fil | tee -a ${logfil}
done;
fi
-----Oprindelig meddelelse-----
Fra: info-ingres-bounces.TakeThisOut@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] På vegne af Laframboise, André
Sendt: 26. januar 2010 21:16
Til: Ingres and related product discussion forum
Emne: Re: [Info-Ingres] Alter table question
If subsequent inserts put's a '1' in the new column, then yes, that's what I'm looking for.
Thanks.
Andre
-----Original Message-----
From: info-ingres-bounces.TakeThisOut@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Biljana Gibarac
Sent: January 26, 2010 3:13 PM
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Alter table question
Ingres 2006 Release 2 SPARC SOLARIS Version II 9.1.2 (su9.us5/100) login Tue Jan 26 15:05:10 2010 continue
* create table mytable (col1 int) with page_size=4096\g Executing . . .
continue
* commit\g
Executing . . .
continue
* insert into mytable values (1)\g
Executing . . .
(1 row)
continue
* insert into mytable values (2)\g
Executing . . .
(1 row)
continue
* insert into mytable values (3)\g
Executing . . .
(1 row)
continue
* commit\g
Executing . . .
continue
* alter table MYTABLE add MYCOLUMN char(1) not null with default; modify MYTABLE reconstruct;\g Executing . . .
(3 rows)
continue
* alter table MYTABLE alter MYCOLUMN char(1) not null with default '1'\g Executing . . .
continue
* update MYTABLE set MYCOLUMN = 1;\g
Executing . . .
(3 rows)
continue
* select * from mytable\g
Executing . . .
┌─────────────┬──────┐
│col1 │mycolu│
├─────────────┼──────┤
│ 1│1 │
│ 2│1 │
│ 3│1 │
└─────────────┴──────┘
(3 rows)
continue
*
André, is this what you are looking for?
-----Original Message-----
From: info-ingres-bounces.TakeThisOut@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Laframboise, André
Sent: Tuesday, January 26, 2010 1:00 PM
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Alter table question
Hi everyone,
I need to add a column to a huge table. I've always copied out/in but now I'd like to experiment with 'alter table'.
That way I don't have to recreate all the rules and other dependencies.
Problem is I need to add a column not null with a user defined default value ('1').
I've read other posts and it seems this can't be done directly but may be possible using multiple steps.
An example I've seen is this;
alter table MYTABLE add MYCOLUMN char(1) not null with default; modify MYTABLE reconstruct; alter table MYTABLE alter MYCOLUMN char(1) not null default 1; update MYTABLE set MYCOLUMN = 1;
Does this really work ?
_______________________________________________
Info-Ingres mailing list
Info-Ingres.TakeThisOut@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
_______________________________________________
Info-Ingres mailing list
Info-Ingres.TakeThisOut@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
_______________________________________________
Info-Ingres mailing list
Info-Ingres.TakeThisOut@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres >> Stay informed about: Alternates in LIKE 
FAQ
Search
Profile
Private Messages
Log in
