Hi

I am trying to update a table with in EGL Community Edition but I am unable to update the same.

I have first created the sql record for the table as below:

record F561733File type SQLRecord{tableNames =[}

10 IHBTCH num(6) {column="IHBTCH"};
10 IHSTS char(1) {column="IHSTS"};

end

Now in my service program i am trying to update the record as below:

execute update
#sql{
update TEST#N34.F561733
set
IHSTS = "S"
} for F561733FileRecord;

but my program fails again and again when I try to update the record.

Could any one please help me in the same.

Thanks,
Vivek

Vivek,

what SQL errors are you getting. That is the key to knowing how to fix it.

Actually I am unable to check for the sql errors.

Could you please tell me the way to check the same also.
Thanks,
Vivek

I checked for sql error. It gives the error code SQL7008.

The exception says:

(string) "EGL0504E EXECUTE: SQL7008 F561730 in TEST#N34 not valid for operation.sqlstate:55019sqlcode:-7008
EGL0002I The error occurred in approveEGLService processing the approveEGL function."

Not sure which database you are using but make sure your table is journaled or otherwise transaction enabled so that the (default) commitment control can do its thing. If you can't journal the table then check the EGL documentation for settings that will turn off commitment control.

I think DB2 400 is the only one that returns a -7008. As Dan pointed out this means journaling is not turned on for the table on the iSeries.

I googled SQLCODE 7008 and a couple of possibilities were:

1. add WITH NONE to the end of your UPDATE statement.

2. Turn off the commitment control using the JDBC properties for the connection (either in the datasource settings or the connectionURL).

Hi

I did not find any option to turn commitment control to off in EGL Community Edition and also the "WITH" keyword was not valid to be used with executre update statement.

You are right that I am using DB2.
Thanks,
Vivek

Vivek,

it is not an option in EGL. You will have to look up the property for the jt400 (assuming you are using this one) jdbc drivers.

Then you put this property on the JDBC driver connection URL (sqlDB build descriptor) or in the datasource if this is a J2EE application.

The other option is to start the journaling for the table on the iSeries.

Are you trying to say that I need to put "CommitmentControl=*none" build descriptor?

At present the exract of my build descriptor is:

<BuildDescriptor
name="iSeriesEGLPoc_Java"
genProject="iSeriesEGLPoc"
system="WIN"
J2EE="YES"
genProperties="GLOBAL"
dbms="DB2"
sqlJNDIName=""
deploymentDescriptor="iSeriesEGLPoc"
linkage="externalPrograms">
</BuildDescriptor>
<BuildDescriptor
name="iSeriesEGLPoc_JavaScript"
genProject="iSeriesEGLPoc"
system="JAVASCRIPT"
J2EE="NO"
genProperties="GLOBAL"
deploymentDescriptor="iSeriesEGLPoc"
>
</BuildDescriptor>
Thanks,
Vivek

Vivek,

No..that is not what I am saying.

The build descriptors you are showing are for generation of JavaScript. Somewhere I assume you also have some used to generate Java as genned JAva is what is able to do the SQL access.

In this build descriptor, you are either specifying a sqlJNDIname (i.e. datasource) or a sqlDB (i.e. connectionURL).

If it is sqlDB, then you add the property from the JDBC driver to this (but you need to look up the property to use..it is not from EGL).

If it sqlJNDIName, then when you will need to modify the properties on the datasource definition to set the property (same one as for sqlDB).

Again, you can also solve all this by turning on journaling on the iSeries side. I am sure the iSeries docs describe how to do this.

Thank you so much for your precious time markevans but I am sorry to say that I have still not got any solution for my problem.

It would be of great help if you could give me any sample code for the same.
Thanks,
Vivek

Hi Vivek,

Is it an option for you to journal the tables you are using? There is great benefit to this as then commitment control can take effect and you can safely perform complex transactions that involve multiple tables.

As for the JDBC options to turn off commitment control (if that is your only option). I don't know if these are the right settings but it appears that maybe you need to add the following to your connection URL:

transaction isolation=none;autocommit=off

For example, in one of by build descriptors I have:

sqlDB="jdbc:as400://www.xxxxxxxxx.com;libraries=somelib;prompt=false"

...and to turn off the use of commitment control I would change this to:

sqlDB="jdbc:as400://www.xxxxxxxxx.com;libraries=somelib;transaction isolation=none;autocommit=off;prompt=false"

Keep in mind that I am just taking a stab at this here. I always use journaled tables and I haven't had a chance to test the options given above. This is just my guess at what you need to do from a driver perspective to turn off the use of commitment control. (As Mark noted, your JDBC connection may be configured in a data source rather than your build descriptor.)

Hope this helps.

--Dan

I want to second Dan's recommendation here - use journaling. It's very simple to do and requires little maintenance. I've been working my way through the whole data connection / data source copncept and it's so much easier to do with journaling. The only downside to journaling is that you will need to manage your receivers, and while it's easy to do it's typically a job done by your systems administration folks (usually whoever does the backups).

Joe

Hi Joe,

You probably know this already: If you are only using journals to enable commitment control (and not using them in a backup/recovery scenario) then you can set the journals to be system managed with auto-deleting receivers based on a maximum receiver size.

On the CRTJRN command:

Manage receivers . . . . . . . . MNGRCV *SYSTEM
Delete receivers . . . . . . . . DLTRCV > *YES
Receiver size options . . . . . RCVSIZOPT *MAXOPT1

Doing this takes the otherwise tedious journal/receiver management out of the equation.

--Dan

Thanks a lot dan.

I just added "auto commit=off" in my jdbc connection properties and the code worked fine now.

Once again thanks a lot to all the buddies for helping me :)
Vivek

Hi Dan,

we tried to turn off commitment control in the build descriptors.

It works in the RBD test environment.

But if we install the project on the websphere application server 8.5.5 it doesn't work.

Kind regards!

Hi. Please follow the code posting instructions noted in the forum header. Otherwise, your code comes across as largely unreadable.

As for trapping errors, for starters, try enclosing your update in a try/onException block:

try   // Your SQL update here onException (exception SQLException)   SysLib.writeStdout("SQL exception: " + exception.message);onException (exception AnyException)   SysLib.writeStdout("Other exception: " + exception.message);end

Hello Vivek,

We also have the same error sometime. We are using constraints, and once in a while the journal, which is needed to check the constraints (and do a rollback) is inactive. Then this error appears.

When you execute a CREATE TABLE in SQL, the table is also added to a journal automatically.
Is it possible for you to start an SQL-session (STRSQL) and try do execute your SQL statement here?
I think the same error will appear there. If this is true, this has nothing to do with EGL.

Greetings, Jeroen