External tables - referencing for information warehouse

Sorry, this is probably a newby question, but if someone could point me to the appropriate SQL commands to review I would appreciate it. (Informix IDS 9.40 on AIX 5L is our setup.) I have a database that (IMO) was designed poorly...we have all of our tables in "datadbs" and the software we use just references everything in there. This includes sales, accounts payable and receivable, G/L, inventory, and various other tables. One table in particular I would like to migrate out of datadbs. The table is called IMAGES_REC and contains a set of TIF files that are usually just saved copies of faxes, proof of delivery, etc. Column name Type Nulls name nchar(30) no data byte yes The table has data that gets loaded with a unique name and the actual TIF file. Other tables are used to store details about the images. For example, FAXES_REC contains a list of faxes that went out, who it was sent to, and the name of the image in the IMAGES_REC table which is a copy of the original fax that was stored for safe keeping. The IMAGES_REC table is written to daily to insert an image, but then only read back once in a blue moon if we ever need to retrieve the image. At month-end time, we issue an onunload of the data and then onload that into another environment to have a frozen copy for reporting purposes. However we will NEVER need to reference the IMAGES_REC table in that EOM environment. What I would like to do is alter the database in a way so that I migrate the IMAGES_REC table into another dbspace or another database. I then do my onunload and onload that skips that table so that it does not waste time having to unload and reload data that is never going to be needed. I realize I have to worry about possible constraint issues. We use level-0 ontape daily for the backups so as long as it is in same schema, that should be unaffected. Any ideas on this? Just point me at the SQL commands I should consider and I'll read up on it. Not afraid of doing the research, just currently getting overwhelmed with commands about data warehousing, external linking, views, and such. TIA. Steve

if you would create another database on the same instance (let's call it "images"), you can move the table there, either via unload/load or direct insert: insert into images:images_rec select * from images_rec Then you can create a synonym to this table in the current database via something like: create synonym images_rec for images:images_rec; and your software will not be affected. Backups via ontape will backup all the data (including the images) since they are in the same instance, while onload will only backup the database you want for transfer to the reporting department. On Jan 29, 4:59 pm, "steven_nospam at Yahoo! Canada" <steven_nos...@yahoo.ca> wrote: > Sorry, this is probably a newby question, but if someone could point > me to the appropriate SQL commands to review I would appreciate it. > (Informix IDS 9.40 on AIX 5L is our setup.) > > I have a database that (IMO) was designed poorly...we have all of our > tables in "datadbs" and the software we use just references everything > in there. This includes sales, accounts payable and receivable, G/L, > inventory, and various other tables. One table in particular I would > like to migrate out of datadbs. The table is called IMAGES_REC and > contains a set of TIF files that are usually just saved copies of > faxes, proof of delivery, etc. > > Column name Type Nulls > > name nchar(30) no > data byte yes > > The table has data that gets loaded with a unique name and the actual > TIF file. Other tables are used to store details about the images. For > example, FAXES_REC contains a list of faxes that went out, who it was > sent to, and the name of the image in the IMAGES_REC table which is a > copy of the original fax that was stored for safe keeping. > > The IMAGES_REC table is written to daily to insert an image, but then > only read back once in a blue moon if we ever need to retrieve the > image. At month-end time, we issue an onunload of the data and then > onload that into another environment to have a frozen copy for > reporting purposes. However we will NEVER need to reference the > IMAGES_REC table in that EOM environment. > > What I would like to do is alter the database in a way so that I > migrate the IMAGES_REC table into another dbspace or another database. > I then do my onunload and onload that skips that table so that it does > not waste time having to unload and reload data that is never going to > be needed. > > I realize I have to worry about possible constraint issues. We use > level-0 ontape daily for the backups so as long as it is in same > schema, that should be unaffected. > > Any ideas on this? Just point me at the SQL commands I should consider > and I'll read up on it. Not afraid of doing the research, just > currently getting overwhelmed with commands about data warehousing, > external linking, views, and such. > > TIA. > > Steve

On Jan 30, 8:53 am, "Zachi" <zklop...@gmail.com> wrote: > if you would create another database on the same instance (let's call > it "images"), you can move the table there, either via unload/load or > direct insert: > > insert into images:images_rec select * from images_rec> > Then you can create a synonym to this table in the current database > via something like: > > create synonym images_rec for images:images_rec;> > and your software will not be affected. Backups via ontape will backup > all the data (including the images) since they are in the same > instance, while onload will only backup the database you want for > transfer to the reporting department. Thanks Zachi - that is way more help than I was hoping for! Appreciate it. I will review the CREATE SYNONYM options and test it out in my training database.

On Jan 30, 8:53 am, "Zachi" <zklop...@gmail.com> wrote: > if you would create another database on the same instance (let's call > it "images"), you can move the table there, either via unload/load or > direct insert: > > insert into images:images_rec select * from images_rec> > Then you can create a synonym to this table in the current database > via something like: > > create synonym images_rec for images:images_rec;> > and your software will not be affected. Backups via ontape will backup > all the data (including the images) since they are in the same > instance, while onload will only backup the database you want for > transfer to the reporting department. Thanks Zachi - that is way more help than I was hoping for! Appreciate it. I will review the CREATE SYNONYM options and test it out in my training database.