Re: IBM i connection with MS access -- MIDRANGE-L

Hi,

I was able to generate this error when from MS Access when I selected path
from MS Access like below way:-

External Data --> New Data Source-->From other Sources-->ODBC Database

and then from this Window (Get External Data - ODBC Database) if I select
below option:-

Select the source and destination of the data

option :- Link to the data source by creating a linked table.
Access will create a table that will maintain a link to the
source data.

Also I noticed this error occurs when there is an attempt to write update
any file which is not journaled( in general testing)

But I have a case where it was found that a logical file gave this error
(SQL7008)

whereas it's dependent PF was journaled as followings:-

1) file is journaled :- yes
2) access path journaled: no
3) Access Path : Keyed
4) Implicit access path sharing : Yes
5) Access path journaled : yes
6)object restored with partial transactions, apply journal changes required
: *No

It's dependent LF where this error is noticed (while trying to update it
through MS Access) has below properties related with Journaling:-

1) Access path journaled : NO
2) Access Path: Keyed
3) Implicit access path sharing : Yes
4) Access path journaled : yes

So it appears in the above case where a PF is journaled like above then
also it's dependent LF while trying to update/write records in it through
MS Access giving this SQL7008 error, so not sure what to do to resolve it
in this type of case?

Thanks.

On Mon, 28 Mar 2022 at 23:21, Peter Dow <petercdow@xxxxxxxxx> wrote:

Hi Jerry,

Which situation(s) do you think should be generating the SQL7008 error?

Since you're not getting the SQL7008 error, are you getting any other
errors?

If you're not getting any other errors, are you able to access the data?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx

/
On 3/28/2022 5:03 AM, jerry ven wrote:

Hi,

Could someone please advise on this?

Thanks.

On Sat, 26 Mar 2022 at 18:21, jerry ven<jerryven95@xxxxxxxxx> wrote:

For the file which is not journaled and even journaled, I tried all the
below Commit mode options in Advance server options still could not
generate this error :-

Commit mode:

Commit immediate(*None)
Read uncommited (*CHG)
Read commited(*CS)
Repeatable read (*ALL)
Serializable (*RR)

***************************
also tried by changing various Connection types available under IBM i
Access Windows ODBC Driver Connect:

1) Use ODBC access mode
2) Use ODBC access mode, Call allowed
3) Limit to Read/Call(Select and Call statements allowed)
4) Limit to Read-Only (Select statements only)

But could not generate that error(SQL7008).

Thanks.

On Fri, 25 Mar 2022 at 22:03, jerry ven<jerryven95@xxxxxxxxx> wrote:

Hi,

I tried commit mode (*CS) then opened the file in MS Access, which was
not journaled and then entered some value in this file's field and then
clicked on save button on MS Access but could not generate this error.

So How could I generate this error any example with proper steps.

and then in the same example by changing commit mode settings or using
any other settings how to resolve below error as mentioned earlier:-

"ODBC- update on a linked table Libraryname_ Filename' failed.
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008- Filename in
libraryname not valid operation.(#-7008) "

Thanks.

On Fri, 25 Mar 2022 at 19:30, Rob Berendt<rob@xxxxxxxxx> wrote:

<snip>
Commit Mode :- Read uncommitted(*CHG) -- > I think I was advised for

the

same option (Commit immediate (*NONE) among different available

options

under the drop down button here ?
</snip>
"Read uncommitted" and that *NONE I would think would both turn off

the

requirements for journaling.

Now, let me say that I think journaling is a good thing. We journal

all

of our files. It took me years of fighting and it wasn't until we

got a

H/A system which required it that it happened. If you do use

journaling I

strongly recommend getting
Resource
ID Option Feature Description
5770SS1 42 5117 HA Journal Performance
It will cost you a little but it's much cheaper, and much more
effective, than throwing a truckload of money into alternative

hardware.

I suppose you could customize all your vendor software to better

handle

commitment control (as was once suggested to me by someone on the

list) but

that's very costly, hampers upgrades, etc. Probably only acceptable

to

those shops which figure manpower is unlimited but paying 1 cent to a
vendor is prohibited. Or maybe a consultant milking business.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
jerry ven
Sent: Friday, March 25, 2022 9:32 AM
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx

Subject: Re: IBM i connection with MS access

CAUTION: This email originated from outside of the organization. Do

not

click links or open attachments unless you recognize the sender and

know

the content is safe.

Hi,

Please find the settings from 'MS access to IBM i' as followings and

I am

not sure how and from where to check settings for 'and/or SQL server

to

your IBM i' :-

MS Access to IBM i :-

1) MS Access blank database -->New Data Source --> From Other
Sources-->ODBC Database--->Import the source data into a new table in

the

current database.

2) Import the source data into a new table in the current database -->
Click on 'OK' --> DSN Name --> New --> iSeries Access ODBC Driver
(32-bit)(
Under Advanced File DSN Creation Settings by default it's written

(under

Type driver - specific keywords and values: DRIVER = {Client Access

ODBC

Driver (32 -bit)} and verify this connection(recommended) checkbox is
tick
marked.

3) Then type the name of the file data source Which you want to save
this
connection to. Or, find the location to save to by clicking

Browse.---> I

typed some name here.

4) Then I Clicked on the Finish button.

5) Then On 'IBM i Access for Windows ODBC Driver Connect' Window

below

are the settings for 'General' and 'Server' tabs:-

For General :-

Data Source name -- I think i can use the same Data source name which

i

gave on step number 3) as mentioned above ?

Description:- it's coming automatoically as 'IBM i Access for Windows
ODBC
data source'.

system:-- name of iseries server on which i want to make this

connection.

Connection options:- I have let the default 'Use IBM i Navigator
default'.

security :- 'Use same security as IBM i Navigator connection'.

For Server :-

Naming convention:

by default it's coming as 'SQL naming convention (*SQL)

Default SQL Schema or Library:- I have mentioned the library name in
which i am doing this testing for updating records to a specific file
which
is journaled and not journaled.

Connection Type:- Use ODBC access mode

undee Advanced server options Window below are the default settings:-

Commit Mode :- Read uncommitted(*CHG) -- > I think I was advised for

the

same option (Commit immediate (*NONE) among different available

options

under the drop down button here ?

Time :

format :

hh:mm:ss (*HMS)

Separator:

:(Colon)

Date :

Format:
yyyy-mm-dd (*ISO)

Separator
. (dash)

----
----

Thanks.

On Fri, 25 Mar 2022 at 18:11, Rob Berendt<rob@xxxxxxxxx> wrote:

I would check your setup in your connection from MS Access and/or SQL
server to your IBM i.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf

Of

jerry ven
Sent: Friday, March 25, 2022 8:15 AM
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: Re: IBM i connection with MS access

CAUTION: This email originated from outside of the organization. Do

not

click links or open attachments unless you recognize the sender and

know

the content is safe.

1.) where can i check this connection options like
SET OPTION COMMIT = NONE ?

2.) also does this error appear when 1) trying to update any file

using

MS access which is not journaled or 2) does it appear when

someone

runs

SQL query to update the same file which is not journaled using MS

access

?

Thanks.

On Fri, 25 Mar 2022 at 16:48, Rob Berendt<rob@xxxxxxxxx> wrote:

But he was concerned because the error was NOT appearing.

There are options in SQL, like WITH NC, which turns off commitment

control

and thus cares not a whit about journaling.
There also connection options like
SET OPTION COMMIT = NONE
which will turn off commitment control too. I believe this

capability is

also in your connection setup.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf

Of

Jack Woehr via MIDRANGE-L
Sent: Thursday, March 24, 2022 3:53 PM
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx

Cc: Jack Woehr<jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx>
Subject: Re: IBM i connection with MS access

CAUTION: This email originated from outside of the organization. Do

not

click links or open attachments unless you recognize the sender and

know

the content is safe.

"This error comes from the database itself : the SQL7008 message is

usually

a journaling issue indicating that either the updated file or

database is

not journaled, or that the current environment does not have

authority to

its journal."

https://www.ibm.com/support/pages/error-javasqlsqlexception-sql7008-during-rule-execution-server-diagnostic

Looks like you're trying to do something in SQL you can't do on a

file

that's not jourmalled.

On Thu, Mar 24, 2022 at 11:22 AM jerry ven<jerryven95@xxxxxxxxx>

wrote:

I tried updating a file using MS access and the file was not

journaled

but

when i clicked on save button in MS access after making any data

entry

in

any of the field which was opened in MS Access for a particular

file

then

this error ' "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008"

was

not

appearing.

--
Jack Woehr, IBM Champion 2021-2022
<

https://www.youracclaim.com/badges/528d23d6-087f-4698-8d17-d59688106ac4/public_url

Absolute Performance, Inc.
12303 Airport Way, Suite 100
Broomfield, CO 80021

NON-DISCLOSURE NOTICE: This communication including any and all
attachments is for the intended recipient(s) only and may contain
confidential and privileged information. If you are not the

intended

recipient of this communication, any disclosure, copying further
distribution or use of this communication is prohibited. If you

received

this communication in error, please contact the sender and

delete/destroy

all copies of this communication immediately.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com