Re: A shot from the hip (with shotgun :-))

If this is a critical, repeating and possibly growing task, you might want to explore an ETL ( Extract/Transform/Load ) tool like Datastage from Informix ( actually now from Ascential, Informix's Solutions division) This will allow you to more easily design and execute this type of task ( as well as many other things)
Take a look at

http://www.ascentialsoftware.com/solutions/datastage

for more info...Its not cheap, but if you need it, it seems to be the best....

"Frank" <franjoe_at_frisurf.no> wrote:

>Hi!
>
>Your actual business problem is not quite clear to me :-)
>but I can try to point at some pitfalls/questions:
>
>First note; if your tables contains LONG columns the INSERT INTO ... SELECT
>* FROM
>will notoriously make problem (read: is not allowed).
>If you have a few tables having LONG columns it might be a good idea to work
>a bit on those early.
>
>1) in some of the info you have entered it looks like you are going to copy
>info from
>several tables into one single one by just copying one and then the next
>asf. If all your 150 tables have the same columns (they are the same table
>in a way),
>then inserting in this way would make sense; a statement along the lines of
>
>INSERT INTO temptable
>SELECT * FROM table1
>UNION
>SELECT * FROM table2
>UNION
>SELECT * FROM table3
>.....asf
>
>could help you. If some of your tables have different types or
>different number of columns this may become a lot of work, because in
>UNION's
>each query set must contain the same number of columns, and they must be of
>the same type.
>Snapshots may also be worth a look, instead of INSERT.
>
>
>2) the more common type of problem is that all the tables are
>going to be joined together in one query, or more likely groups of tables
>are going to be join together to form several queries,
>and their result is to be inserted into temp tables for later use in
>reports.
>
>INSERT INTO ...
>SELECT a.col1, a.col2,
> b.col1, b.col2, ...
>FROM a, b, c, ...
>WHERE join...join...join
>
>is still possible, also here snapshots are worth a look. Your main
>challenge here is to join correct :-|.
>
>
>The source code for this development object will probably become large.
>
>Another thing that comes to my mind when we are talking about 100-150
>tables is the performance of this. Keeping an eye one the time consumption
>issue (you probably want the logic to complete within 1 hour ?)
>Trying to split the job in several parts that can be performed separately
>might be
>a good idea.
>
>Also if you choose to use TRUNCATE on the tables bear in mind that this
>statement performs a implicit commit, which may leave some of your reports
>blank
>in other oracle sessions until all the information is regenerated.
>
>If you choose insert-solution also keep an eye on the ROLLBACK segment
>resourse.
>
>These kinds of issues are relevant both if you choose stored procedures or
>sqlscripts.
>
>
>I m not sure that I have answered any of your question, but ...:-)
>Frank
>
><vikasgupta_at_excite.com> wrote in message news:968ug1$44o$1_at_nnrp1.deja.com...
>> hi Frank,
>>
>> I agreed with your views, creating stored procedure is better idea than
>> SQL script. I am creating this for inserting data into temporary table
>> pulling data from more than 100 tables. If I am writng stored procedure
>> I need to use more than 150 'Insert statement as select from' I have to
>> open 150 cursor may be more than this. I am creating all this for a
>> Crystal Report. wouldn't the procedure be very long. Or all this will
>> be ok.
>>
>> Any suggestions.
>> Thanks
>>
>> In article <iwbh6.2640$xT3.73490_at_news1.oke.nextra.no>,
>> "Frank" <franjoe_at_frisurf.no> wrote:
>> > Hi!
>> >
>> > We have also choosen to prefer stored procedures instead of scripts.
>> >
>> > The reason is you get fewer development environments (only Oracle
 database
>> > not UNIX). This means that you
>> > simply have fewer things to keep in order. Keep the Database running
 an the
>> > code is there (and working ?-).
>> > It is also easier to copy the database to test/development
 environment,
>> > because everything is copied in the same
>> > way and everything is in the Database. With SQLPlus you sometimes must
>> > create temporary files/scripts
>> > to achieve things - another thing to remember when moving/copying a
>> > database.
>> >
>> > Business logic have a tendency to grow in complexity, PLSQL is easier
 to
>> > allow business logic to "grow" in
>> > (become more complex). SQLPlus scripts are easy when the problem is
 easy,
>> > but very hard when the problem grows.
>> >
>> > Actually we would not have choosen the cron solution either, we would
 have
>> > used the DBMS_JOB. I believe the
>> > application becomes easier to document/hand over to other people, when
>> > everyting(?) is in the Database.
>> >
>> > Frank
>> >
>> > <vikasmamta_at_my-deja.com> wrote in message
>> > news:95sfc6$72g$1_at_nnrp1.deja.com...
>> > > Hi Alex
>> > >
>> > > Thanks for the help.
>> > > Writing SQL script is more easier. My boss wants me to write a
 stored
>> > > procedure. I want to know would the stored procedure run from cron
 or
>> > > not. Can I tell him it is better to run SQL script than stored
>> > > procedure.
>> > >
>> > > Thanks in anticipation.
>> > > In article <95s7hu$v5d$1_at_nnrp1.deja.com>,
>> > > Alex Filonov <afilonov_at_pro-ns.net> wrote:
>> > > > I don't think you really need a stored procedure. May be sqlplus
 script
>> > > > will work for you. In that script you can easily use truncate.
 It's
>> > > > also easier to run sqlplus script from cron.
>> > > > One more suggestion. Even if you decide to use stored procedure,
 use
>> > > > insert into <temp table>
>> > > > as select ....
>> > > > statements. It'll save you time.
>> > > >
>> > > > In article <95p2l8$4ik$1_at_nnrp1.deja.com>,
>> > > > kushan <vikasmamta_at_my-deja.com> wrote:
>> > > > > Hi
>> > > > >
>> > > > > I am creating a stored procedure to select the data from 5
 tables &
>> > > > > then inserting into one table. And I want to run this procedure
 hourly
>> > > > > by setting its cronjob. Can you check my syntax
>> > > > >
>> > > > > Create or replace procedure P1
>> > > > > Begin
>> > > > >
>> > > > > Select ......... from ..... where...=....;
>> > > > > Insert into temp table ........;
>> > > > > Select ......... from ..... where...=....;
>> > > > > Insert into temp table ........;
>> > > > > ....like this many selects & inserts......
>> > > > > end;
>> > > > >
>> > > > > Is that necessary to give parameters in or out or would it work.
>> > > > >
>> > > > > one more thing Everytime when the procedure will run it will
 get the
>> > > > > completedata from different table & insert it into temp table so
 that
>> > > > > temp table keep on getting duplicated rows. If I create a
 primary
 key
>> > > > > in the temp table would that help me. Or do I need to use
 truncate.
 If
>> > > > > So where i need to write this truncate in the procedure. Please
 help
 me
>> > > > > out with this problem> I am in real trouble.
>> > > > >
>> > > > > Thanks
>> > > > >
>> > > > > --
>> > > > > RICHARD
>> > > > >
>> > > > > Sent via Deja.com
>> > > > > http://www.deja.com/
>> > > > >
>> > > >
>> > > > Sent via Deja.com
>> > > > http://www.deja.com/
>> > > >
>> > >
>> > > --
>> > > RICHARD
>> > >
>> > >
>> > > Sent via Deja.com
>> > > http://www.deja.com/
>> >
>> >
>>
>> --
>> RICHARD
>>
>>
>> Sent via Deja.com
>> http://www.deja.com/
>
Received on Mon Feb 12 2001 - 21:01:25 CET