we are relatively new to TOAD and I would like to join data in .csv and excel files directly to DB2 tables in a single view. My attempts at this generated errors with ‘cross query engine’ issues and in the short-term I simply imported those csv, excel and DB2 files into temporary MS-SQL tables and then did the joins within MS-SQL. Our goal would be to have our end-users able to create these types of .csv/excel to DB2 query views in TOAD and they do not have access to MS-SQL. can you provide direction on how we might do this in Toad? Ideally if you could just direct me to the (videos or quick-start) sections of the TOAD help for this topic, I can start there and save us all some time.
thanks!
our license info: Toad Data Point Base Edition, version 3.8.0.575
Someone pointed out to me that you have the Base Edition. Cross-Query is only offered in the Pro edition. You can enter the Trial Key below and it it a try.
I was going to go thru it again to make sure I got the exact steps but here instead is my recollection of what I’ve made work in the last couple days since I posted this forum question.
what didn’t work:
open a .csv in excel and SAVE AS .xls file
select the columns and create an excel table (in that same .xls file). save the file again.
go into TDP connection manager under Microsoft excel connections…and add or change it to see my new .xls file.
(even though I have the base edition) I was able to ALT-C to open new cross connection qry editor in TDP query builder.
anything I tried there, errored becuase of the ‘type in excel’ was unrecognizable (sorry didnt get screen shot of error)
what did work:
instead of doing a SAVE AS, I created a new .xls sheet and then copied & pasted into the new sheet from the orig CSV file in excel.
then I did the same for steps 2,3,4 above and THEN I was able to ‘see’ the excel table on the TDP left side and then drag and drop that table into the ALT-C editor to finally do a SQL select.
that worked great, speedy, etc.
NEXT I did the same on the DB2 tables and also could quickly get SQL select results.
I tried to select from the same XLS file and INNER JOIN the DB2 table to it. I found that it was DEALTHLY Slow. my original csv file had <600 rows in it and the DB2 table had 180K rows. at first I thought it wasn’t working. Then I shrunk the XLS table…when I removed all rows except 2 rows. THEN I finally got an inner join to work, but it took 34 seconds. I realize that this could be related to our network being tightly locked down, but I don’t see the TDP slowness on either the XLS selects or the DB2 selects…only when there’s a JOIN there.
is there some default TDP setting that I can adjust so this cross connection editor might not be so slow?
thanks for your (patience on my slow reply and for your) help.
We used to have Cross-Connection in base. But it uses Microsoft Jet engine but it was very slow. Please enter the trial key below, This will turn on Pro edition. Now you will see Cross-Connection Query Builder from tool menu. This will be faster. I would not expect the same speed of joins as if it was in the same database. But the Pro engine will be huge improvement over base.