Teradata: Extract month and year from date

 extract (year from date1)

 extract (month from date1)

Teradata: Backup table with data

create table table1_bkup as
table1 with data

Teradata: List columns of a table

select
*
from dbc.columns
where tablename ='table1'
and databasename = 'database1'

Teradata: List tables in database

select
*
from dbc.tables
where databasename='database'
and tablekind = 'T'

Teradata: Add column with default value

alter table table1 add col1 int default 0

Teradata: Rename table

rename table table1 to table2

Teradata: Add column

alter table table1 add x int;

Teradata SQL: Drop column

alter table table1 drop col1

Teradata SQL: Update column name

alter table table1 rename col1 to col1_updated

Teradata SQL: Create table using Select statement

Execute following 2 SQLs:

1. create table tablex as
(
select
a,
b,
c
from table1
)
with no data;

2. insert into tablex
select
a,
b,
c
from table1;

Teradata recursive sql

Suppose we have a table:
create table accont_tbl
acct_id int,
manager_acct_id int,
name varchar(50)
)
which contains employee records with their supervisor.

Then the recursive sql is:
with recursive mgr_tbl(acct_id, manager_acct_id, mgr_name, depth) as
(
select
a.acct_id,
a.manager_acct_id,
a1.name as mgr_upn,
1 as depth
from account_tbl a
inner join account_tbl a1
on a.manager_acct_id = a1.acct_id
where a.acct_id = 8976
union all
select
mgr_tbl.acct_id,
a.manager_acct_id,
a1.name,
mgr_tbl.depth+1
from mgr_tbl
inner join account_tbl a
on mgr_tbl.manager_acct_id = a.acct_id
inner join account_tbl a1
on a.manager_acct_id = a1.acct_id
-- where mgr_tbl.mgr_upn <> 'email of root if root's manager is same as accout'
)
select * from mgr_tbl order by depth

Regular expression to remove blank lines

If you are using Notepad++, replace ^[\n\r]+ with nothing in the replace

Regular expression to remove html and get only values from a select list

Regular expression : ^<.*>

Replace ^<.*> with blank if we use Notepad++

Example:
<option value="/html/A.htm">A
<option value="/html/B.com">B
<option value="/html/C.htm">C

The replace will produce:
A
B
C

Teradata sql - retrieve top and bottom 50 percentage of the records

Top 50%
----------
select top 50 percent  *
from table1
order by id asc

Bottom 50%
---------------
select top 50 percent  *
from table1
order by id desc

Update from select

Update table1
from
(
select
t1.x,
t1.y
from table2 t2
left join table3 t3
on t2.id = t3.id
) input
set f1 = input.x
where f2 = input.y

Insert into with select

Insert into table1
(
field1,
field2,
field3
)
select
t2.x,
t2.y,
t3.x
from table2 t2
left join table3 t3
on t2.id = t3.id

Remove carriage returns from a field

select oTranslate(afield, '0A0D'xc,' ') from atable

Scheduling daily backup job using crontab

1. cd /etc/cron.daily

2. Create a symbolic link to the job

ln -s /home/auser/mysqlbackups/db_to_s3_backup.sh db_backup

This will make it run at 4.02am daily per crontab definition

> cat /etc/crontab

...

.....

# run-parts

01 * * * * root run-parts /etc/cron.hourly

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

~

...

Crontab format. Ref: https://www.pantz.org/software/cron/croninfo.html

# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
    0        2          12             *                *            /usr/bin/find

Centos - running commands at system initialization

Did this to restart DB and Web server during system boot.

1. Create a initialization script at /root/instance_init.sh

Commands are chained using && to make sure it executes in sequence

#!/bin/bash
echo "Step: staring mysql" &&
service mysqld start &&
echo "Step: restoring database" &&
runuser -l auser -c '/home/auser/mysqlrestore/s3_to_db_restore.sh' &&
echo "Step: starting jboss" &&
runuser -l auser -c '/usr/local/jboss-6.1.0.Final/bin/startjboss.sh'

2. Open > vi /etc/rc.d/rc.local
append a line:

sh /root/instance_init.sh

Backup and Restore MySql backup to/from AWS S3

Steps followed from a Centos 6 machine:

1. yum install s3cmd
2. Created a configuration file .s3cfg under user's home directory.
Defined access and secret keys in the file.
access_key=<access key>
secret_key=<secret key>

3. Created a backup script file:
#!/bin/sh
mysqlpass="your password"
db_schema_name="your db schema"
s3bucket="s3://your bucket name"
user_home="/home/auser"
script_home="/home/auser/mysqlbackups"
backup_file="some.backup.sql.gz"

mysqldump -u root -p$mysqlpass $db_schema_name > $script_home/temp.sql
gzip $script_home/temp.sql
rm -rf $script_home/$backup_file
mv $script_home/temp.sql.gz $script_home/$backup_file
/usr/bin/s3cmd -c $user_home/.s3cfg put $script_home/$backup_file $s3bucket/$backup_file

4. Created a restore script file:
#!/bin/sh
mysqlpass="your password"
db_schema_name="your db schema"
s3bucket="s3://your bucket name"
user_home="/home/auser"
script_home="/home/auser/mysqlbackups"
backup_file="some.backup.sql"

cd $script_home

/usr/bin/s3cmd -c $user_home/.s3cfg get $s3bucket/$backup_file.gz

gzip -d $backup_file.gz

mysql -u root -p$mysqlpass $db_schema_name < $backup_file

rm -rf $backup_file