RE: [SPAM] SQL SERVER/ISERIES INTEGRATION

[CWilt@xxxxxxxxxxxx]

Elvis,

The problem is the original poster is inserting from an SQL server via a
"linked server".

So she's not going to have the control over the connection she would via
ODBC/JDBC.

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx]
> Sent: Tuesday, November 09, 2004 11:26 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: [SPAM] SQL SERVER/ISERIES INTEGRATION
> 
> 
> Charles, 
> 
> Per:
> 
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_74b
> w.asp?frame=tr
> ue
> 
> one cannot set it to NONE and I don't think NONE is SQL 
> standard either.
> However, due to legacy apps on the 400 not using commitment control or
> journaling, NONE was introduced in DB2/400. 
> Rosalie is creating and inserting into a table on the 400 so 
> the command
> should be perfectly valid (i.e. via ODBC connection).
> 
> You are correct on journaling just the table she created, that would
> definitely work.  You're also right on no planning required for that
> solution.
> 
> When I said planning I was really thinking of changing the 
> application to do
> it the "right" way (per SQL standards) -- establishing 
> commitment control
> boundaries, selecting which tables need journaling and which 
> not etc.  All
> good things but require planning and strategic placement.
> 
> Elvis
> 
> 
> -----Original Message-----
> Subject: RE: [SPAM] SQL SERVER/ISERIES INTEGRATION
> 
> Elvis,
> 
> Can you use SET TRANSACTION ISOLATION LEVEL NONE on SQL server???
> 
> I didn't see any mention of this being valid during my quick 
> scan of the
> documentation.
> 
> 
> Also, just wanted to point out that the original poster 
> doesn't need to
> journal the entire library.  Just the files he is trying to 
> write to from
> SQL server.  No real planning is required.
> 
> Charles Wilt
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>  
> 
> > -----Original Message-----
> > From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx]
> > Sent: Tuesday, November 09, 2004 10:43 AM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: RE: [SPAM] SQL SERVER/ISERIES INTEGRATION
> > 
> > 
> > Rosalie,
> > 
> > CREATE COLLECTION and CREATE SCHEMA are SQL standards and 
> > automatically
> > start journaling in that collection/schema/library, again per 
> > SQL standards.
> > CRTLIB is OS400 native command and does not comply with SQL 
> > standards (nor
> > should it), so it does not automatically start journaling.
> > 
> > By default, most ODBC or JDBC drivers have AutoCommit set 
> to true and
> > Transaction Level set to something other than NONE (NC).
> > 
> > If you want to create new table in a library created using 
> > CRTLIB command
> > and update its data (i.e. via INSERT), then you need to tell the SQL
> > driver/server/connection not to AutoCommit and set the 
> > Transaction Level to
> > NONE.
> > 
> > SQL command is SET TRANSACTION ISOLATION LEVEL NONE.
> > 
> > Alternatively, you could start journaling on that library, 
> > but that is more
> > involved solution and requires some planning.  Not to say 
> that there's
> > anything wrong with it :)
> > 
> > Elvis
> > 
> > 
> > -----Original Message-----
> > Subject: [SPAM] SQL SERVER/ISERIES INTEGRATION
> > 
> > I am trying to export data from a table on a sql server 
> > database using a
> > linked server to our iSeries.  The table gets created however 
> > it does not
> > populate the data and I get the following message :-
> > 
> > (Embedded image moved to file: pic12859.pcx)
> > 
> > None of the files or libraries that we need to populate on 
> > the iSeries are
> > journaled and we cannot journal them as they are part of an 
> > ERP package.
> > When I create a library through a SQL statement using "create 
> > collection
> > testlib" and try to populate the table it then works, however 
> > when trying
> > to update an existing table in an existing library it does 
> > not.  I notice
> > that the library "testlib" is created with journals.
> > Can anyone help please?
> > 
> > Rosalie Ducasse
> > 
> > 
> > 
> 
> 
> 
> 
> --
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>