Hai all..

I am working on a report where the databases are IMS on mainframe. I am trying to join 2 tables but the report keeps running with no output.

Should we special any different approach for IMS databases as they are hierarchical?

Any suggestions appreciated.
I can provide more details if needed..

Thanks..

This message has been edited. Last edited by: Kerry,

You have to make sure the JOINs are efficient.

If you're using WebFOCUS, you can turn SQL traces on to see the SQL generated by WebFOCUS, along with any relevant messages from the SQL translator.

(Always add this at the top of your code if you're accessing DBMS tables:

-*-- Set up SQL tracing - as provided by Francis Mariani on FocalPoint ---------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/CLIENT

-*-- Show SQL generated sub-statement trace ----------------
-*SET TRACEON = STMTRACE/2/CLIENT

-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

(Leave the commented lines in comments as these trace statements generate a lot on unnecessary lines)

Also add

SET XRETRIEVAL=OFF

just before your TABLE FILE and leave it there until you have made the code efficient. This command "previews the format of a report without actually accessing any data".

And remember, IMS is not an SQL-based data base. Is there a way to turn a trace on the backend (how are you getting to IMS or are you running a reporting server on the mainframe?). You really need to see what IMS is getting. Maybe your IMS DBA, also, could help you out.

quote:

IMS is not an SQL-based data base

Wow! It's been years (decades?) since I last worked with IMS, I didn't realize!

Thanks for replying Francis

It says Retrieval killed and 0 records..?!

That's the result of the XRETRIEVAL=OFF - the request does not retrieve and data. If that's all you got, then it's because, as Ginny pointed out, IMS is not a SQL-based database.

BUT

According to the Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS, Version 7 Release 6.1 documentation,

quote:

You can issue any SQL request through the Adapter for IMS

Take a look at the above mentioned manual. There is some discussion of traces in IMS, but it does not seem to be SQL tracing. There are some optimization suggestions that might be interesting to look at.

Francis,

Yes, you can issue SQL for IMS, but it wont be passthru!

Enigma,

Can you get output from each IMS table (they were called segments)?

If you can, then does your JOIN command take into account:

quote:

when you join TO an IMS database, you must join to one of the
following:
· A key field or secondary index in the root segment (ALIAS= IMSname.KEY,
IMSname.HKY, or IMSname.SKY).
· The high-order portion of a key field or secondary index in the root segment

Look at the IMS interace pdf.

I am able to get data from each tables but joins fail.
I guess I am writing joins in wrong way as I write for SQL databases (relational)

I am trying to join 2 IMS tables.
7 fields from each tables are used as join as said to me.

What could be the general format for joining these?(using segment name etc.)

I never worked on IMS. Spare me please..

JOIN COL1 AND COL2 AND COL3 AND COL4 AND COL5 AND COL6 AND COL7 IN TBL1
  TO COL1 AND COL2 AND COL3 AND COL4 AND COL5 AND COL6 AND COL7 IN TBL2 AS J1
END

I think this should work.

You also have to pay attention to what Danny said. Working with IMS is very tricky so it is imperative that you read the IMS Interface or adapter manual or the chapter in the Data Administration manual related to IMS. You need to be able to look at the setup for each file to make sure that the join can be done.

Or you can extract the data separately then do the join in WebFOCUS with your hold files.

Francis, that is the normal way which is not working for this IMS database..

it seems a bit strange to me that you have to join on 7 fields. As far as I can recall, IMS did not deal with multiple field joins.
I think you should take a hard look at the masters of these 2 segments or maybe post them here.
If you still are stumped, could you go the way Ginny suggested: extract, hold and then join?