I realize that this is my 400th post, and I have yet to start a thread ... so here goes.

Anyone with experience using SAME_DB and DB2 as the client. This is for WF 7.6.4 on Windows (IIS and Tomcat fronting it). Connection to DB2 is via the CLI interface on Windows connecting to DB2 on a mainframe.

What we are trying to do is create a hold file using SAME_DB, and then joining the result to another DB2 table. The table is created and populated, and the master and access files are created and valid. When I try to run the request, I get nothing as the output ... and I mean nothing, no html, no messages, nada!

There seems to be some confusion with my New York friends as to weather this is actually supported without writing SQL pass through. The odd thing about this is when I test it against a MySQL data source, it works flawlessly.

Any thoughts?

Below is a sample of the working MySQL example:

SET PAGE=NOPAGE
TABLE FILE wf_rb_report_attr
PRINT *
ON TABLE HOLD AS T1 FORMAT SAME_DB
END
TABLE FILE wf_rb_report_attr
PRINT *
ON TABLE HOLD AS T2 FORMAT SAME_DB
END
JOIN
LEFT_OUTER REPORT_ID AND COLUMN_NAME IN T1 TO MULTIPLE
     REPORT_ID AND COLUMN_NAME IN T2 AS J1.
END
TABLE FILE T1
PRINT *
END

I have had major problems with this feature against Teradata starting back in 7.1.1. We are now on a 7.6.5 reporting server with a 7.6.2 front end and the feature is still not usable. You get a report but it has double page numbers. I have had a case open for forever on this.

1 PAGE 1 1 PAGE 1 PAGE 1

JOINED REPORT

TRAINID EVT_DT EVT_TM EVT_CD EVST_CD DEST_CD CAD_CREW_CD CAD_CREW_NME
CNAMCEB051A 2008/02/17 15:22:00 TD WO TA 01 CJMARTIN
CNAMCEB054A 2008/02/18 19:28:00 TD WO TA 01 JDWEISHAAR
ECEBNAM047A 2008/02/16 23:35:00 TD WO TA 01 JREDLEN
ECEBNAM049A 2008/02/17 20:04:00 TD WO TA 01 JMBRANER
ECEBNAM050A 2008/02/18 17:25:00 TD WO TA 01 JDUTTER
ERTRCDM007A 2008/02/17 23:30:00 TD WO TA 01 KESTAMBAUGH
GEDUCEI316A 2008/02/17 21:13:00 TD WO TA 01 JDUTTER
HGALMAD116A 2008/02/16 23:23:00 TD WO TA 01 KESTAMBAUGH
UBIRSEM025T 2008/02/16 20:52:00 TD WO TA 01 JDUTTER
UKEEMAD028T 2008/02/17 18:42:00 TD WO TA 01 GLWINCHESTER

See bold above. If you do a view source on it, you will see that it creates an html report for each SAME_DB request. PDF doesn't work at all; I get garbage.

We have DB2 (mid-tier and mainframe) but I haven't tried it against it. If you would like, I could try it and let you know. Let me know.

Haven't done it myself, but it was tried here by others. Biggest issue, permissions on the DB2 side and I can't remember the other. Since I am doing this from home. May find out tomorrow.

Ginny,

It's the same in DB2. We too have a case open on SAME_DB -- the "1 PAGE 1 PAGE" echo is a bug and a sloppy one at that. Because it is hardcoded to output "1 PAGE" to the screen when you use SAME_DB, it breaks the HTTP mime type directive, which is why PDF comes out as garbage. It also breaks XLS and any other output format that requires a mime type sent to the browser.

We reported it 6 months ago, and it is sitting with a "In Product Division" state, going absolutely nowhere. Which is a real shame, the ON TABLE HOLD FORMAT SAME_DB holds such promise as a very useful feature to speed up processing by keeping Hold files in the RDBMS as temporary tables. Why would you provide such a useful feature then not bother to fix such a sloppy, major bug that makes unusable??? (Can you sense the frustration.....)

Dhagen, good luck, don't plan on using any output other than HTML, maybe your voice can add some clout to getting attention provided from New York.

-- Dan

University of Nebraska at UNO
WF 764 Linux Tomcat 5.5/BID/DB2/MySQL
DataMigrator 764 Linux

I agree wholeheartedly with you, Dan. I opened my first case on this feature in the summer of 2006 when basically it wasn't working at all. The original case that I opened on the 1 PAGE problem got 'accidentally' closed and a new one had to be opened.

I too had great hopes for this feature. Our biggest source for reports here is a huge Teradata data warehouse. Some of the views have millions of rows. And often, some of the data relationships that need to be created require concatenated fields. The developers have to bring the data back from multiple views independently, mess around with the keys, then do the join in WF. The SAME_DB feature would have eliminated many steps.

I too am frustrated. Is anyone out there listening...........?

i'd use sql passthru

select t1.*, t2.* from wf_rb_report_attr t1 left outer join wf_rb_report_attr t2 on
t1.REPORT_ID = t2.report_id AND
t1.COLUMN_NAME = t2.column_name

I'm not sure why you're referencing the same table without any where clause, then doing an outer join on it.

The same table with an outer join was only a test to show the functionality. NY says that this is suppose to work. I'm going to give it a couple of more kicks and verify that there isn't a timing issue happening here or something.