DSXchange

Agree; you cannot bypass the columns, which is what COUNT(*) does (these days). But ORDER BY is wrong for different reasons. The method described above (MIN every column) forces Oracle to read every projected column (including user-defined functions and scalar sub-queries) without the additional ove...

I'm sorry, did I miss the meeting where we had to stay on-topic and avoid interesting conversations. ;) I came in late and cannot see some of the posts, so I'm not sure whether we are saying that ORDER BY is a good method or a bad method of ensuring all rows are fetched. To be clear, it is BAD for t...

The SELECT COUNT(1) thing is a myth. Oracle is getting a bit clever nowadays for the SELECT COUNT(*) FROM (...) wrapper. It can almost always work out which columns will not actually be projected, and then no select them. As a workaround, in SQL*Plus you can save your SELECT statement in a script, s...

Tap, tap, tap. Is there an echo in here? I thought that's what I said Apols to Ray if I repeated his response, but (like the OP) I was unable to read it all.

I guess I wasn't clear enough.

I don't use DB2, but it should be similar to the Oracle OCI stage. I believe that what you are trying to do is impossible. It calls for the table name to be dynamically determined at run-time. The closest you can get to a dynamic table name is to use a Job Parameter (eg. #TABLE_NAME#) in your SQL st...

An hour does seem a bit excessive, but I haven't tried anything quite so large. Still, you'd think it would scale. When you say you have 500 derivations, does that mean you have 500 columns in your target, or 500 stage variables. What is the nature of these derivations? Are they calls to routines? C...

If you don't want to use a hased file or aggregator, then I hope your source file is pre-sorted. :wink: Here's some ideas: - If your source is sorted, you can use Stage Variables in a transformer to save the PK of the previous row, and check it against the PK of the current row. If it is the same, r...

I've had no reason to handle the encrypted params - the only ones I use are always $PROJDEF - no exceptions - ever. In testing the script I noticed that the value returned by DSGetParamInfo() for encrypted params was not the value you see in the DSParams file. It was just 2 or 3 chars of rubbish (ra...

At the risk of being lynched, I'll throw in my 2c: First, I agree entirely with Ray's suggested approach. It is proven and scalable - it most certainly CAN be done with Hashed files. However, the OP's approach is also valid and scalable. I know because that is the way I built my current DW. But it's...

I could be wrong, but 5000/s is pretty darned fast (excuse my dockyards language) for an Update Else Insert job. Is it possible that it used to be Insert Only or Update Only and has recently been changed? My experience is with Oracle, not DB2, but I have found Array Size / Transaction Size ineffecti...

I wasn't advocating putting any DDL in the DataStage job to be created at runtime. The function and type DDL statements are executed once only during setup. The only runtime SQL is the SELECT .. FROM TABLE(func(..)).

I think the OP gets this and is on the right track.

Also worth noting that a lot of Windows servers may not be running an FTP Listener. To make sure you CAN do it, at the Unix prompt, type
ftp <servername>
and see if it asks you to log in. If not, speak to the administrator of the Windows box.

If it works, go for it. I thought it would need a constructor. I still haven't unravelled all of the wonders of Oracle's object-relation features.

OK. So you have a SP like this: CREATE PROCEDURE my_proc(p1 IN NUMBER, rc OUT sys_refcursor) ... Create a type that contains the same columns as the cursor, and a pipelined function to fetch the cursor. CREATE TYPE my_typ AS OBJECT ( col1 VARCHAR2(20) , col2 NUMBER ); CREATE TYPE my_typ_ary AS TABLE...

Look in the Oracle PL/SQL doco for pipelined functions. You could create an interface that essentially converts the REF CURSOR into a View, then select from the VIEW in a normal OCI stage.