Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4936
  • Last Modified:

how store the result of DB2 SQL statement in shell variable

Hi Experts,

Good Morning..  First of all i am thankful for  your quick responses for my earlier questions.

Now i am having one requirement  on shell script, which i am not familiar. Hope you will provide solution as usual.

I am having one DB2 database. In which i have to execute SQL query  there and the result of the query have to use as input to SQL query  in ORACLE.

Presently i do not have any dblinks (heterogeneous) etc..  I would like do that in shell script.

Is there any way i can store the result of query in shell variable and use that variable in oracle to get the output.

Thanks,
Chanikya.








Thanks,
Rama.
0
chanikya
Asked:
chanikya
  • 5
  • 5
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Rama.

The first step is getting the data out of DB2.  :)  You should be able to do that with the command line processor.

  db2 "connect to mydatabase"
  db2 "Select count(*) FROM mydatsbase.sometable"

Those statements will return (display) the count selected, along with the column headers.  To put that value into a variable is just standard shell scripting.

 
  db2 "connect to mydatabase"
  CountVar=`db2 -x "Select count(*) FROM mydatsbase.sometable"`

You can then use the variable as any other shell variable.


Good Luck,
Kent
0
 
chanikyaAuthor Commented:
Hi  Expert,

The actual db2 query return array of elements instead of single value. And also  would like to know how i can use that arrya in my oracle query.

The actual requirement is
First i have to run the SQL in db2
select empno from emp;

Next i have exclued those employees from my oracle query.

select * from emp where empno not in ( output of db2 query).

Can you please guide me.

Hello Zone Adviser,

It is also oracle related. i would like to notify the oracle experts also. Can you please add to oracle zone also.


Thanks,
chanikya.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Chanikya,

I'm not aware of any way to store a DB result set into a shell array.  That said, you CAN write the results to a host file as a CSV file, and let Oracle load from the CSV file.

Are you running licensed copies of DB2 and/or Oracle?  Licensed DB2 allows you to Federate other database engines.  They could be another DB2 database, an Oracle database, etc.  If the Oracle server is licensed, it too allows you to connect an external database.

But I don't believe that the free versions of those two database engines support that kind of connect.

  db2 => EXPORT TO {filename} OF DEL MODIFIED BY COLDEL, {SELECT statement}

That statement will write all of the selected rows to your CSV file.


Will that work for you?

Kent
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
chanikyaAuthor Commented:
Hi Expert,

I am sorry. I can not directly compare the CSV data loaded from db2  in oracle query. For that first  i need to create a table(EXTERNAL) in production oracle database. which i do n't think  i will get an access to production . Because it is one time requirement.

But both databases  all are licensed versions . Can you provide me  a thought bit simpler than this..



Thanks
Chanikya.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hmm.....

I guess I'm not understanding something.  

What's your process that writing data to a shell array going to work for you, but writing the data to a file and using the shell's tools to process it one line at a time not going to work?

Given what you just said, I don't think Federation is the answer.  It's probably the best answer, but since this is a production database it may be tougher to get the necessary permission to do that than to create another table.

Are there any schemas on the production database that you can use?   An Oracle query across schemas would be trivial.

About how many lines of data do you expect to move to Oracle?  How many columns per row?


Kent
0
 
chanikyaAuthor Commented:
Hi Kent,

Thanks a lot for providing different solution. But give me two days of time to implement . As i am waiting for permission to both db2 and oracle databases.

Frakly speaking.. I am totally new to db2 database. Even  i donot know how i can connect to db2 database.  Incase of oracle it is required  oracleuser/password@tnsalias (to connect remote database). But i do not know anything about db2 database.

However i have to finish this given requirement.


Thanks
Chanikya.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Chanikya,

In a lot of ways, Oracle and DB2 are very similar.  They do mostly the same things and the user/application side is controlled by SQL (though both have different extensions to ANSI SQL).  From an administrative viewpoint, they are quite different and their code-bases are completely different.

The databases handle user user security quite differently, but both require a user name and password.  DB2 defaults to the operating system for userid/password controls, Oracle defaults to its own storage of user controls.  Both have the ability to connect to Microsoft's Active Directory so that the user ID and password can be synchronized with the Microsoft controls.

It will take a DBA will full administrative rights to connect Oracle and DB2 together.  Depending on your environment, this could be an issue, or it could be something that has long-term benefit.  Only your site's management can make this call.


Kent
0
 
chanikyaAuthor Commented:
Hi Kent

Thank you for giving all the information.  Now i got the access to db2. db2 client installed on my machine and they have given the name of the database and said that they have given access to my user.

now i am trying from command editor  as below..
db2 connect to databasename

But i am not able to connect.  what is missing here?  i doubt, we have to provide server and port details. where  can I provide?.

I am really Sorry for asking this low level of details.

Please help me in this regard.

Thanks
Chanikya.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Chanikya,

The full syntax of the connect statement is:

  db2 connect to databasename user {username} using {password}

If you don't specify a username and password, your current credentials are passed to DB2.  You'll need a username on the DB2 server that matches your username to use the short form for the connect command.

Kent
0
 
chanikyaAuthor Commented:
It works but i am checking the feasibility.

Thanks
Chanikya.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now