This document outlines the approach to implement a process that automatically updates an array based User Defined Function based on an insert into a database table. The documented example is for Timezones. The extent to which this process is followed depends on the change management requirements and the nature of the individual UDF. For example the process could end at the point at which the C program is created which is then Unit Test prior to Production deployment. The example was developed in a Teradata environment but should be applicable to other RDBMS’.
<<Timezone UDF Automated Recompilation v1.0.doc>>
Timezone UDF Automated Recompilation v1.0.doc
Copy the following to a text file, update the file_name with the name of the file that you want to count the rows of and save it as Row_Count.PL.
#!/bin/perl
open(INFILE, "<g:\File_Name.TXT") or die "Can’t open input.txt: $!";
open(OUTFILE, ">g:\File_Name_count.TXT") or die "Can’t open output.txt: $!";
$count = 0;
$line = <INFILE>;
while ($line ne "") {
$count = $count +1;
$line = <INFILE>; }
print OUTFILE "$count";
#!/bin/perl
open(INFILE, "<g:\Insert_Filename.TXT") or die "Can’t open input.txt: $!";
open(OUTFILE, ">g:\Insert_Filename2.TXT") or die "Can’t open output.txt: $!";
$line = <INFILE>;
while ($line ne "") {
$pad = <UPDATE with length 154 > – length ($line);
chomp $line;
$new_line = $line.(" "x$pad);
print OUTFILE "$new_linen";
$line = <INFILE>;
}
How to run the Teradata Reserved Word Script prior to an upgrade.
The purpose of the reserved word script is to identify any database objects that contain reserved words that may cause issues when the database is upgraded.
The reserved word script is provided by Teradata customer services engineers performing the upgrade and will be named something like : pre_upgrade_prep.pl
- Step 1 – Log onto the <SYSTEM> DEV ETL server, navigate to the directory in which the pre_upgrade_prep.pl file is located. Launch a command prompt.
2. Step 2 – For the <SYSTEM> DEV system enter the following at the prompt. pre_upgrade_prep.pl <system>_dev/dbc,[dbc password] <SYSTEM>_DEV.log
[The output for Steps 2, 4, 6 will be a set of scripts specific to the environment. The output files are listed below:
- udd_out.rpt
- dbql_out.rpt
- dbc_role.rpt
- reservedwords.rpt
- rep_recovery.rpt
- pwdchgdate.rpt
- mds_recovery.rpt
- trigger_out.rpt
- upgrade_estimate.rpt
- sp_nospllist.txt
- test_<env>.log
The number of output files may change with future releases of Teradata but the list above is current for Teradata 12.]
3. Step 3 – When the script has completed copy the DEV output files, including the LOG file into a DEV specific directory as the output files will be overwritten by subsequent runs of the file.
4. Step 4 – For the <SYSTEM> PROD system enter the following at the prompt.
pre_upgrade_prep.pl <system>_PROD/dbc,[dbc password] <SYSTEM>_PROD.log
5. Step 5 – When the script has completed copy the PROD output files, including the LOG file into a PROD specific directory as the output files will be overwritten by subsequent runs of the file.
6. Step 6 – For the <SYSTEM> DR system enter the following at the prompt.
pre_upgrade_prep.pl <system>_DR/dbc,[dbc password] <SYSTEM>_DR.log
7. Step 7 – When the script has completed copy the DR output files, including the LOG file into a DR specific directory as the output files will be overwritten by subsequent runs of the file.
8. Step 8 – Zip the 3 folders and email them to the Td CS engineer.
The following is a list of scripts that are used in upgrading or migrating to Teradata Database Release 12 and their purpose.
Name Purpose File Name Date
Reserved Words Script Finds V12 Reserved Words check_reserved_words.bteq 09/17/07
Reserved Words Short List Shows new words for 12 reserved_words_short_list.txt 09/17/07
Stored Procedures Inspection Script Finds Teradata Stored Procedures
That will not recompile nospl.bteq 08/18/04
Triggers Inspection Script Finds triggers that may violate 6.x rules trigger_inspection.bteq 06/13/06
Upgrade Estimate Script Produces upgrade estimate data for v12estimator.xls upgrade_estimate.bteq 06/13/06
UDD Character Columns Inspection Script Inspects UDD character columns before upgrade 12.x udd_inspec_standard.bteq
udd_inspec_japanese.bteq 09/22/08
09/22/08
Store
Procedures
Source files
for UDD SPs with dynamic
SQL instead of
repeating submit
SQL statements chkambiguous
chkdupnames
chkfor1a
chksbc
chktranserr
concatstr
instmptbl
chkforb5 07/07/08
09/17/07
09/17/07
07/07/08
07/07/08
07/07/08
07/07/08
09/17/07
UDD
Character
Sets
Inspection
Script Inspect character
sets installed
on the system
before pre-upgrade udd_check_charset.bteq
02/06/08
Pre-Upgrade
Preparation
Script Inspects DBS readiness
before upgrade by
running the following
scripts automatically:
check_reserved_words.bteq
trigger_inspection.bteq
nospl.bteq
upgrade_estimate.bteq
udd_inspec_standard.bteq on non-Japanese system
udd_check_charset.bteq on Japanese system
udd_inspec_japanese.bteq on Japanese system pre_upgrade_prep.pl 11/24/08
Fill this in
Fill in later
David Pracy 