Shell Programming and Scripting

Hi,

We have a requirement like, to check the datatypes of columns against database.

After loading the sample data in to one of the database, need to compare the datatypes of the columns are matching with the provided files.

Is there a way that we can achieve through shell scripting.

We are using Netezza as database.

Any help/inputs are really appreciated.

Thanks in advance.
Samah

Do you mean as an example:
You expect a date datatype. Does the date have to be valid?

Or do you simply want to make sure no number datatypes have problems?

To be honest I have worked with databases for a VERY long time, and the databases have always caught datatype mismatches. The requirement sounds more like really uniformed management decision.

In any case, please give us some simple examples of what you need to check.

Jim,

Thanks for the quick response!

Actually once the sample data has loaded, i will be performing some manual QA checks to make sure the data received as expected. For example, duplicates check,any column has null values,...etc.,

One of the validation check, i need to make sure the datatypes of the all columns of a table are also matching against database based on data feed requirement document.

Thanks.

You can normally define a few things to help you, such as unique constraints and defining the column as not accepting nulls.

It then depends on your loader if it pushes in the records it can and reports the anomalies, or if it just fails altogether at the start or with partial data loaded.



Robin

What Robin is saying - set the database metadata (column descriptions) to values that actually enforce datatyping, like DATE, NUMBER(5). When you insert data, the db will check to be sure data is correct in terms of typing. Instead of you having to do it manually later on. There are constraints that you use as well when you first create your new table - like NOT NULL, UNIQUE (for keys), and dependancy - key in child table has to match existing column value in parent table.

If you allow weak datatyping, like setting a column to allow any text, then anything can and will be inserted into that column. And you have a mess to clean up.

Thanks Robin and Jim.

My task is to check whether the datatypes are correct after the ETL load.

I got your point but how to automate the below step through shell scripting.

"One of the validation check, i need to make sure the datatypes of the all columns of a table are also matching against database based on data feed requirement document."

Would it be possible to give an example for this?

For example: I am using the below datatypes.

Code:

INTEGER (9)
CHARACTER (100)
DATE (yyyy-mm-dd)
NUMBER (17,4)

Thanks.

If you describe the table and compare it to your document, would that do what you need? To automate that, you would need to get the document into a simple format that matches the structure displayed when you describe the table.


I'm still not sure on your real needs or if this is management paranoia.

Robin