14 replies to this topic

[Bryan Thompson]

I'm having a few issues getting pro-iv to connect to multiple sql servers... i've created the odbc dsn and everything but it seems to have a problem in the ini file... I can only connect to whatever database is specify for SQL_DBNAME and I can't have multiple SQL_DBNAME entries.. If anyone has gotten this to work could you please send me a copy of your ini file.. I've read the documentation and the posts on here, nothing seems to work.

Thank You

Bryan Thompson

[Wim Soutendijk]

All I have managed to do is to move the SQL_DBNAME to an .ini file that is set in the INIPATH variable in each [USER] section.
That allows me to access diffent SQL database, but only one per PRO-IV session.

I don't think PRO-IV supports multiple dabases from one session.

[Ralph Gadsby]

I suggest you read chapter 6 of the Environment Guide, PRO4.INI Settings and ODBC Data Source Setup.

[Dennis John Laceda]

Same here. I am able to connect to multiple database. but again only one database per session.

If you are able to make it work as one session and multi-database please inform us (via this forum) how. But honestly i doubt if it's even possible.

[Ralph Gadsby]

It is, and always has been for Oracle, possible to connect a single PROIV process to multiple database connections. It is also now possible to connect a single PROIV session to multiple SQLServer connections. I regularly connect to multiple databases concurrently. All the information that is required to set this up is in the standard PROIV documentation set.

[Ralph Gadsby]

Rommel, can you tell me what version of the manual you are looking at and precisely where in the document this text appears? I have searched both the 4.6 and 5.0 documents and cannot find this text.

[Ralph Gadsby]

OK, I've found it at the end of the chapter in the 4.0 documentation. This restriction does not appear in later versions of the documentation.

When connecting to multiple databases in a single PROIV session you should be aware of the potential problems with commiting data across multiple database instances.

[Bryan Thompson]

ok I finally found the answer to this.. Ralph I don't beleive its acutally in chapter 6 of the documentation... it looks like its supposed to be but the documentation appears to be wrong. the trick is to set up your ini file as you would with a single database using SQL_DBNAME = your default database name then in your new Database section in the ini the connection string must be username/password/ODBC SYSTEMDSN its the second slash that makes it work then in your file definitions use ÛNAME%Filename...

Hope this helps looks like I wasn't the only one with this problem

Bryan Thompson

[Dennis John Laceda]

How difficult it could be when you want to commit under this situation? I was in the impression that it will just follow the pre-determined commit rule within PROIV.

Unless of course you are issuing a imbedded COMMIT command in PROIV itself. Which i myself am trying to avoid and i also tell my team to do so. Since you require to understand the PROIV cycle to be fully aware of where to properly put it. But even me eventhough am aware of the cycle i still try not to code this in.

Cheers!

[DARREN]

The danger that Ralph is warning of is a subtle one and can occur with implicit or explicit commits. Take the example of where an update is updating two files on two different databases (A) and (. PROIV issues the updates (writes) and both records appear to have been written. Then the commit is issued (either implicitly or explicitly). The RDBMS’s will then take the record out of the rollback section and write it to the physical file. There may be enough space to do this for the first file and the record is successfully written to the database (A). The second file write, however, may fail as the RDBMS moves it from the rollback segment into the database (. The record is therefore not written and a rollback is performed. As the record on the first database (A) was written successfully and committed, that record will not be rolled back and you potentially have two databases that are now out of sync.

[Dennis John Laceda]

By the looks of it we don't have much control over it. Cause it's not just memory. Say the Database (A) has been written and committed but when it was B's turn something happened, whatever that might be, that causes it to do a rollback.

At the end of the day i guess if we can avoid doing a update on two database then don't do it.

[Richard Bassett]

If you update more than one database within a transaction and you want to retain inter-database consistency in the event of failure, some part of the underlying software must perform a TWO-PHASE COMMIT (usually abbreviated 2PC).

Traditionally, 2PC is done by Transaction Processing Monitors (sometimes more generally referred to as Transaction Managers or TMs). These are products such as CICS or Tuxedo. ProIV does not work with Transaction Managers except on the IBM mainframe.

However, nowadays most databases have a 2PC capability. For example if you update multiple Oracle databases VIA A SINGLE DATABASE CONNECTION then Oracle should provide 2PC automatically for you.

Oracle may also be able to provide 2PC with other databases if you use the right 'gateway' products - I'm not certain. I have not investigated what other databases provide.

Notice that it is vital to do the work via a single database connection - otherwise the databases don't even know their work is part of the same transaction (there are ways around this but it's too complicated to get into here and you don't want to go there..)

ProIV, as far as I am aware has no 2PC capability of its own so if you connect to multiple databases directly from ProIV (which seems to be the topic here) you definitely will not get 2PC.

HTH. Richard