Can anyone explain me about SAMEDB command in webfocus and how it can be used in fex files for Hold File reusage?

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

SAME_DB allows you to hold temporary tables within the DBMS from which you are extracting data. It can be volatile, Global Temporary, or Permanent.

It is available for DB2 (on z/OS, UNIX, and Windows), Informix, Microsoft SQL Server, MySQL, Oracle, and Teradata.

The usage is -

ON TABLE HOLD [AS filename]FORMAT SAME_DB [PERSISTENCE persistValue]

There is so much to understand about this I would suggest that you obtain the detail direct from the manual. I believe that it came in with version 7.1.n as I can not find reference to it in the 5.3.2 manuals. ALternatively use the Tech Documentation link at the top of the page.

T

You can get more information by looking up the docuementation. The link is at the top of the FocalPoint page. Search on the term "SAME_DB". If you have release 5.x you can just try out the command on a dummy request to see if it gets by the parser. If it doesn't then you will find it in the 7.x release.

SAME_DB is there since 715.

Not all the options are available for all DBs (e.g. Oracle doesn't know volatile).

And in 715 there is a bug: you can TABLE from such a HOLD table. But you cannot JOIN this table to other DB tables. This is true for DB2 volatile. It should be fixed in 76.

This is my first experiences with SAME_DB.

Thanks for your information.

Rolland,
Can you please give an example how do we use SAME_DB on TABLE HOLD in oracle normally?Will it help me in any way?Please give your suggestions.

I am assuming the user ID used in the request would need create table access, which, in my experience, the DBA rarely grants.

Francis,
Can you please tell me where will you give userID in the syntax?Can you please give an example?

Suji, I don't know anything about SAME_DB, but if the command writes to the database the TABLE request just read from, I am assuming some kind of update privileges are required. The User ID would be the one connecting to the database, it would depend on how security is set up in the Oracle adaptor connection.

Francis,
Thanks for your update.Is there any other way to save HOLD file generated in the current request so that we can reuse it in the next request?

SAME_DB is intended to create temporary tables. In Oracle and DB2 (I don't know nothing about other DBMS) they are created in special places where you normally would have write access to (anyway it is always up to DB admins and company DB policies, but I guess normally they are not so strict for those temporary things).
With option Global Temporary the data is truncated at the end of the session, but the table definition (i.e. the structure) stays. This causes an error the next time you want to use the same name. Or you DROP the table using SQL Passthrou.
With option Volatile (available for DB2, not for Oracle, others I don't know) data and table definition are DROPed at the end of the session.
I think Masters are deleted always at the end of the session.
Option Persistent let the table live forever (I don't know what happens to the Master). Therefore it is stored in the personal tablespace of the active DB user, not in that temporary space.

My personal summary of all this:

• Volatile would be the one I was looking for, as it is behaving like a real HOLD file.
  
• Global Temporary is something in between, I don't know what for.
  
• Persistent is (in my opinion) about the same as HOLD FORMAT SQLxxx.
  

But now something else:

quote:

Originally posted by Suji:
Is there any other way to save HOLD file generated in the current request so that we can reuse it in the next request?

This is something different. If you want to reuse the results in a further request, there are many other possibilities maybe better than SAME_DB.

Depending on what you are doing in the other request you could:

• APP HOLD appname and then ON TABLE HOLD AS myhold FORMAT ALPHA (this saves holdfile and master in the app-folder appname)
  
• FILEDEF myhold DISK path/myhold.ftm and then ON TABLE HOLD AS myhold FORMAT ALPHA (this saves only the holdfile in path, therefore you would have to prepate the master manually. Or you additionally issue APP HOLDMETA appname: this saves only the master to appname)
  
• both of the above you could vary by using ON TABLE HOLD AS myhold FORMAT SQLxxx (this would save the data in an table in a DB of your choice instead of writing them to a file)
  

Of course the last one again has the problem of writing access to the DB.

I hope this helps a little bit

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

We are using this in WebFOCUS 7.6.2, against DB2 and MySQL, using the following syntax:

ON TABLE HOLD AS HOLD1 FORMAT SAME_DB PERSISTENCE VOLATILE

It is WONDERFUL, in that it creates a temporary table on the RDBMS, and then we can use it in subsquent joins against regular tables. It has sped up processes considerable, totally knocks out our un-optimized cross-platform joins between the WebFOCUS server and the RDBMS.

One problem, though, pretty big for us. There is a bug in the usage of this, at least in 7.6.2 -- there is a write to the browser window ("1 PAGE 1") that is hardcoded each time you use the FORMAT SAME_DB. This has the effect of breaking any subsequent output transformation you might want, like putting the final output to PDF or EXL2k.

We've opened up a case on this, but after several exchanges with examples it appears that this is a known issue to programming, at least that's what our case rep is telling us.

Argh, it holds such promise for our shop, but we need variable output (HTML, PDF, EXL2K) in our WebFOCUS reports.

Regards,

-- Dan

University of Nebraska at Omaha
Prod: WF 7.6.2 Linux BID/MRE/Data Migrator
Test: same

I have created HOLD files that are temporary Microsoft SQL Server tables and then used them in subsequent JOINs. This is in WebFOCUS 5.3.2, without SAME_DB. "It is WONDERFUL" and "it has sped up processes" considerably. This may be MS SQL Server syntax only - the HOLD file becomes a temporary table because of the # in the HOLD file name:

TABLE FILE MSEXT_LISTMEMBERVIEW
SUM
LISTNAME
LISTNAMESORT
BY LISTID
BY ENTITYID
WHERE LISTCREATEDON GT DT(&LISTCREATEDON);
WHERE LISTID IS-NOT MISSING
ON TABLE HOLD AS #HOLDC1 FORMAT SQLMSS
END

The one thing you have to watch out for is that you shouldn't turn SQL traces on if you expect a lot of output rows - a SQL Insert command is generated for each row.

Hey that's pretty handy Francis, but correct me if I'm mistaken but aren't the tables created permanent as a result of the "FORMAT SQLMSS"? We could do the same in our DB2 production environment but then I would get my knuckles cracked with a steel ruler by the DB2 admin who doesn't allow end-users to create permanent tables scattered all over the prod database instance.

Even trying the FORMAT DB PERSISTENCE VOLATILE results in insufficient privs to issue a CREATE TABLE.

That's why we're stuck with FORMAT SAME_DB PERSISTENCE VOLATILE. The temporary tables (and synonymns) are allowed under the mechanisms employed (DECLARE GLOBAL TEMPORARY TABLE with ON COMMIT PRESERVE ROWS option).

Two steps forward, one step back. Always is our challenge....

-- Dan

University of Nebraska at Omaha
Prod: WF 7.6.2 Linux BID/MRE/Data Migrator
Test: same

Francis or anyone else that knows

Where is it documented that the # in the HOLD file name creates a temp table? I'd like to see if that is available for Oracle. Also, by temp table, I assume you mean a table that gets deleted automatically at the end of the .fex. without any additional coding. The other thing I would have to consider is the Oracle log files. Does creating the table and inserting rows create log entries?

The # is SQL Server specific. Any table created in MS SQL with a # in the first character of the name is assumed to be a temp table, and will be written to the temp table space.

In other words, this really has nothing to do with WebFOCUS, and therefore not available with Oracle.

dhagen,

Thanks for the clarification. I'm sure that I learnt about the # from you.

Cheers,

dhagen,

Thanks for the reply. I guess it was just wishfull thinking on my part.

John

It's been a while since this thread has been updated. Have any of you been able to get SAME_DB to work? There is another thread about the same topic that was last updated in February of 2008 and it looks like nobody had it working then either.

I'm posting in this thread because I have a question regarding the temporary table naming in SQLMSS. Francis posted an example using

ON TABLE HOLD AS #HOLDC1 FORMAT SQLMSS

. My question is this, how do you reference the temporary table in subsequent queries? I've tried both #HOLDC1 and HOLDC1 and I always get a FOC205 THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLDC1.

What am I missing?

Thanks!

Dan

Dan,

I can't help with SAME_DB, but here's a snippet of code using the temporary MS SQL Serve tables - in both WF and SQL:

-*-- Extract List & List Members from CRM ------------------
-*   (Only those that have not previously been copied from CRM to EIDW)
SET SQLENGINE = SQLMSS
SQL SET SERVER EDWCRM
-RUN

TABLE FILE MSCRME_LISTMEMBERVIEW
SUM
	MIN.LISTNAME
	MIN.LISTNAMESORT
	MIN.BUSINESSUNITNAME
	MIN.STATUSCODE
	MIN.DELETIONSTATECODE
	MIN.LISTCREATEDON
	MIN.LISTMODIFIEDON
	MIN.USERID
	MIN.MEMBERCREATEDON
	MIN.MEMBERMODIFIEDON
BY LISTID
BY ENTITYID
BY LISTMEMBERID
WHERE
  (LISTCREATEDON GT DT(&LISTCREATEDON) OR LISTMODIFIEDON GT DT(&LISTMODIFIEDON) OR MEMBERCREATEDON GT DT(&MEMBERCREATEDON) 
   OR MEMBERMODIFIEDON GT DT(&MEMBERMODIFIEDON) OR DELETIONSTATECODE EQ 2)
WHERE LISTID IS-NOT MISSING
WHERE ENTITYID IS-NOT MISSING
WHERE LISTMEMBERID IS-NOT MISSING
ON TABLE HOLD AS #HOLDC1 FORMAT SQLMSS
END
-RUN

-IF &RECORDS EQ 0 GOTO TEST_END;

-*-- Prepare a file of Lists to be deleted from EDWCRM --------------------------
-*-- Change ID 002 - Delete the List & List Members before adding/updating
-*   them to ensure that members deleted in CRM are also deleted in EDWCRM

TABLE FILE #HOLDC1
SUM 
	LISTNAME NOPRINT
BY LISTID
ON TABLE HOLD AS #HOLDC2 FORMAT SQLMSS
END
-RUN

-*-- Delete the List and List Members ------------------------------------------
SQL DELETE FROM EDWCRM.dbo.CRMListMember
WHERE ListId IN (SELECT ListId FROM #HOLDC2)
END
-RUN

-*-- Prepare a file of List & List Members to be added to EDWCRM -----------------
-*-- Add only the Active Lists
TABLE FILE #HOLDC1
SUM
	LISTNAME LISTNAMESORT
	BUSINESSUNITNAME
	STATUSCODE DELETIONSTATECODE
	LISTCREATEDON LISTMODIFIEDON
	USERID
	MEMBERCREATEDON MEMBERMODIFIEDON
BY LISTID
BY ENTITYID
BY LISTMEMBERID
WHERE ( STATUSCODE EQ 0 AND DELETIONSTATECODE EQ 0 );
ON TABLE HOLD AS #HOLDC1A FORMAT SQLMSS
END
-RUN

-*-- Add the List Members ------------------------------------------------------
SQL INSERT EDWCRM.dbo.CRMListMember
  ( LISTID, ENTITYID, LISTMEMBERID, LISTNAME, LISTNAMESORT, BUSINESSUNITNAME, STATUSCODE,
    DELETIONSTATECODE, LISTCREATEDON, LISTMODIFIEDON, USERID, MEMBERCREATEDON, MEMBERMODIFIEDON )
SELECT LISTID, ENTITYID, LISTMEMBERID, LISTNAME, LISTNAMESORT, BUSINESSUNITNAME, STATUSCODE, DELETIONSTATECODE, 
       LISTCREATEDON, LISTMODIFIEDON, USERID, MEMBERCREATEDON, MEMBERMODIFIEDON
FROM #HOLDC1A
END
-RUN

The logged-in use must have Create Table, Inert, Update, Delete access to the database. Perhaps you're getting an error message?

Aha! I turned on SQL Tracing (but had to leave xretrieval on) in order to get any messages back.

This is what I have...

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
 : Microsoft OLE DB Provider for SQL Server: [] Cannot create new connectio
 : n because in manual or distributed transaction mode.
 (FOC1414) EXECUTE IMMEDIATE ERROR.
  

At least I know what to research next.

Thanks!

Dan

Dan, is it possible you're accessing more than one SQL Server database or environment in the same TABLE or SQL pass-through request? I think I had that problem a year and a half ago - it had something to do with a SQL Server view that joined tables from two different databases - this worked in SQL Server Enterprise Manager but not in WebFOCUS (even when using SQL pass-through!).

I had opened case 41132518 - Access Multiple MS SQL Servers in One SQL Request. There appeared to be no solution, but this was suggested: http://techsupport.information...om/sps/20582053.html. Hope this helps a bit.

It's kind of confusing because I haven't been able to consistently predict the behavior.

I think I was creating a second 'connection' through the adapter by issuing the ENGINE SQLMSS SET DEFAULT_CONNECTION statement.

If I don't use any ENGINE commands I can use the SAME_DB option successfully. However, if I try to run that procedure with the message viewer on all I get back is the aforementioned 1 PAGE 1. If I try to use the SAME_DB format in an existing procedure that has serveral -DEFINE and -SET statements and some Dialog Manager stuff all I get back is the 1 PAGE 1 result.
It will require more troubleshooting to figure out what part of the procedure is in conflict with SAME_DB.

With regard to using ON TABLE HOLD AS #MYHOLD FORMAT SQLMSS:
In my EDASPROF.PRF file I have the statement ENGINE SQLMSS SET CURSORS CLIENT. I put this there as a result of doing research on some previous SQL Passthru issues. However, it looks like the very thing that prevents me from using this method.
If I put ENGINE SQLMSS SET CURSORS SERVER in my procedure it will work but takes noticeably longer than the SAME_DB method to produce results.
If I put ENGINE SQLMSS SET CURSORS in my procedure it also works but takes longer than the SAME_DB method.

I also saw some info about setting the FETCHSIZE parameter. This is REALLY SCARY! If I leave the CURSORS set to CLIENT from the EDASPROF.PRF file and set FETCHSIZE to 1000 I get different results every time I run the procedure! Ack!! If I set the CURSORS to SERVER or [BLANK] I get consistent results but the time to return results is not consistent.

Based on all these confusing results I think I'm going to proceed with the SAME_DB method and figure out how to get it to work with my existing procedures!

Whew!

Yikes!