Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Parse through ~21,000 Database DDL statements -- Fastest way to perform search, replace and insert
Top Forums Shell Programming and Scripting Parse through ~21,000 Database DDL statements -- Fastest way to perform search, replace and insert Post 302805653 by madhunk on Friday 10th of May 2013 02:42:17 PM
Old 05-10-2013
Parse through ~21,000 Database DDL statements -- Fastest way to perform search, replace and insert
Hello All:

We are looking to search through 2000 files with around 21,000 statements where we have to search, replace and insert a pattern based on the following:

1) Parse through the file and check for CREATE MULTISET TABLE or CREATE SET TABLE statements.....and they always end with ON COMMIT PRESERVE ROWS;

Code:
CREATE MULTISET VOLATILE TABLE vt_test
, NO FALLBACK, NO JOURNAL, NO LOG AS
(
                select col1,max(col2) as col2 
                from vt_test
                where col3 in (1,2,3)
)
WITH DATA 
PRIMARY INDEX (col1) 
ON COMMIT PRESERVE ROWS;

2) Replace WITH DATA to WITH NO DATA. If there is already NO DATA, skip changing it.

Code:
CREATE MULTISET VOLATILE TABLE vt_test
, NO FALLBACK, NO JOURNAL, NO LOG AS
(
                select col1,max(col2) as col2 
                from vt_test
                where col3 in (1,2,3)
)
WITH NO DATA 
PRIMARY INDEX (col1) 
ON COMMIT PRESERVE ROWS;

3) Add an INSERT statement right after this....with the same table name. Basically, take the SELECT part from above and end it with a semi-colon. The challenge is code is not formatted. It can be in one line...and can be a mix of CAPS and SMALL (case insensitive). Final code should look like this:

Code:
CREATE MULTISET VOLATILE TABLE vt_test
, NO FALLBACK, NO JOURNAL, NO LOG AS
(
                select col1,max(col2) as col2 
                from vt_test
                where col3 in (1,2,3)
)
WITH NO DATA 
PRIMARY INDEX (col1) 
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_test
select col1,max(col2) as col2 
from vt_test
where col3 in (1,2,3);

4) Output a new file in another directory....

I am comfortable doing the pattern replace using awk, but not well versed with doing the additional step. Please see below and see if you can help.

Code:
#!/usr/bin/ksh
#|------------------------------------------------------------------|
#|  Split the CREATE TABLE AS into DDL and DML Step
#|------------------------------------------------------------------|

usage ()
{
     echo " Usage: $0 <SRC_DIR> <TGT_DIR>"
}

if [ $# -lt 2 ]; then
        usage
        exit;
fi

SRC_DIR=$1
TGT_DIR=$2


for i in *.prc
do
awk 'BEGIN{IGNORECASE=1} {gsub(/WITH DATA/,"WITH NO DATA");print}' $i > TGT_DIR
done

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

how to insert line break + string in vi (search & replace )

Hello all i have big test file that has allot of structure text something like this : <foo1 *.html> <blah action> somthing 1 somthing 2 </blah> </foo1 > now i will like to insert 2 more lines of text below the <blah action> so it will be like : <foo1... (1 Reply)
Discussion started by: umen
1 Replies

2. Shell Programming and Scripting

How perform search & replace with pattner that contains \n?

In emacs I perform a non-regex search and replace where the pattern is ' + ' and the replacement text length is zero. Note that the first and last characters in the search pattern are apostrophes. How can I write a bash script to automate this search and replace using... (1 Reply)
Discussion started by: siegfried
1 Replies

3. Shell Programming and Scripting

Fastest way to list a file in a folder containing 800,000 files using wildcard

Hi, I have a directory with possibly around 800,000 files in it. What is the fastest way to list file(s) in this directory with a wildcard. for example would ls -1 *.abcdefg.Z or find . -name "*.abcdefg.Z" be the fastest way to find all of the files that end with .abcdefg.Z... (6 Replies)
Discussion started by: jerardfjay
6 Replies

4. Shell Programming and Scripting

Help required to parse Oracle imp show=y output to DDL Commands

Hi, I generated an Oracle schema DDL script file using the show=y option of the Oracle import utility but the file that it generates needs a little more formating before we can run this as simple DDL comands to generate the schema at Target using the script file.Here is the simplified output of... (1 Reply)
Discussion started by: rajan_san
1 Replies

5. Shell Programming and Scripting

block of name value pair to db insert statements

Hi, I need to convert the following file into DB insert statements. $ cat input.txt START name=john id=123 date=12/1/09 END START name=sam id=4234 status=resigned date=12/1/08 END (2 Replies)
Discussion started by: vlinet
2 Replies

6. Homework & Coursework Questions

Case statements and creating a file database

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: The assignment is posted below: Maintain automobile records in a database Write a shell script to create,... (1 Reply)
Discussion started by: Boltftw
1 Replies

7. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies

8. UNIX for Dummies Questions & Answers

Grep or sed to search, replace/insert chars!

HI All Im trying to come up with an approach to finding a string, using a portion of that string to insert it on lines starting with the value "GOTO" appending to end of line after removing PT's ( See example below! ) EXAMPLE: 1. I would like to search for the line that starts with "TLAXIS/"... (7 Replies)
Discussion started by: graymj
7 Replies

9. Shell Programming and Scripting

Parse log file to insert into database

I have a log file that's created daily by this command: sar -u 300 288 >> /var/log/usage/$(date "+%Y-%m-%d")_$(hostname)_cpu.log It that contains data like this: Linux 3.16.0-4-amd64 (myhostname) 08/15/2015 _x86_64_ (1 CPU) 11:34:17 PM CPU %user %nice ... (12 Replies)
Discussion started by: unplugme71
12 Replies

10. Shell Programming and Scripting

Read latest log files and perform database insert

Hi Experts, I have a situation where I need to write a shell script to continuously monitor a log directory with multiple log files and perform following: 1. Read the latest log file continuously and grep "Success" OR "Failure" 2. As it capture either Success or Failure, it has to perform a... (1 Reply)
Discussion started by: rish_max
1 Replies

LEARN ABOUT DEBIAN

text::formattable

Text::FormatTable(3pm)					User Contributed Perl Documentation				    Text::FormatTable(3pm)

NAME

       Text::FormatTable - Format text tables

SYNOPSIS

	my $table = Text::FormatTable->new('r|l');
	$table->head('a', 'b');
	$table->rule('=');
	$table->row('c', 'd');
	print $table->render(20);

DESCRIPTION

       Text::FormatTable renders simple tables as text. You pass to the constructor (new) a table format specification similar to LaTeX (e.g.
       "r|l|5l|R|20L") and you call methods to fill the table data and insert rules. After the data is filled, you call the render method and the
       table gets formatted as text.

       Methods:

       new($format)
	   Create a Text::FormatTable object, the format of each column is specified as a character of the $format string. The following formats
	   are defined:

	   l   Left-justified top aligned word-wrapped text.

	   L   Left-justified bottom aligned word-wrapped text.

	   r   Right-justified top aligned word-wrapped text.

	   R   Right-justified bottom aligned word-wrapped text.

	   10R, 20r, 15L, 12l,
	       Number is fixed width of the column.  Justified and aligned word-wrapped text (see above).

	   ' ' A space.

	   |   Column separator.

       head($col1, $col2, ...)
	   Add a header row using $col1, $col2, etc. as cell contents. Note that, at the moment, header rows are treated like normal rows.

       row($col1, $col2, ...)
	   Add a row with $col1, $col2, etc. as cell contents.

       rule([$char])
	   Add an horizontal rule. If $char is specified it will be used as character to draw the rule, otherwise '-' will be used.

       render([$screen_width])
	   Return the rendered table formatted with $screen_width or 79 if it is not specified.

SEE ALSO

       Text::ASCIITable

COPYRIGHT

       Copyright (c) 2001-2004 Swiss Federal Institute of Technology, Zurich.
		 (c) 2009 Trey Harris All Rights Reserved.

       This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

CODE REPOSITORY

       Git - http://github.com/treyharris/Text-FormatTable/tree/master

AUTHOR

       David Schweikert <dws@ee.ethz.ch>

       Maintained by Trey Harris <treyharris@gmail.com>

       Fixed column width and bottom alignment written by Veselin Slavov <vslavov@creditreform.bg>

perl v5.10.0							    2009-07-24						    Text::FormatTable(3pm)
All times are GMT -4. The time now is 06:01 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy