Journaling DB2 files after the fact ...

[Terry Schwarz]

Our use of DB2 is as a client process where a user can specify a specific
collection or library location for a table creation. If the location is a
collection, the table is automatically journaled, if it is a library it is
non journaled. This is also proved out by simple use of STRSQL.

There are various OS400 commands for setting up file journaling, but
specific applicability/usage for DB2 is unclear to me.

Our problem is, that when a non journaled table is updated, a DB2 SQL7905
warning about journaling is issued and mis-interpreted as a serious error
when it is a warning and the transaction gets kicked out. The code within
this particular part of the application layer is not allowed to be changed,
so ignoring the error code is not an option.

I believe this leaves us with the following options/possibilities ... what
I'm interested to know is ...

1. The application allows DB2 settings/commands can be passed to DB2 before
this particular part of the application layer, so can a specific DB2 error
such as this journaling error be turned off so we don't get the error
message in the first place.

2. Can journaling for a specific table in a non-collection library be turned
on after creation and how (if at all) does it differ from a true DB2
journaled table in a DB2 collection (ie ... does it really become a DB2
journal table). Plus can the journaling be then removed afterwards.

3. If #2 isn't possible or enough like DB2, can a library and its tables and
other DB2 objects be turned into a collection after initial creation and
does this create the journals for existing objects (or is that a separate
additional step). Additionally can it be removed afterwards.

Specific syntax would be appreciated, but pointing me to manual section/page
discussing subject will certainly do.

Terry

[walker.l2]

1) I don't think you can suppress errors this way..
2) You can turn journalling on / off for individual files with the
STRJRNPF and ENDJRNPF commands. For almost all scenarios, an AS/400
file (a table in a non-collection library in your terminology) is
exactly the same as a DB2 table.
3) I don't think you can convert a library to a collection
automatically. You would have to rename and recreate.

Have a look at:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp

Walker.

[Kent Millligan]

You could just create a library with a journal named QSQJRN & associated
journal receiver and then the create table operation will automatically
journal even though it's a library instead of a collection/schema.

[Birgitt...@lp-gmbh.com]

I think the following article is also quite helpful.

Flexible SQL Journaling
http://www.mcpressonline.com/mc/.6b3151b7

Steps to create a journal and work with journaling:
1. Create a Journal Receiver: CL-Command CRTJRNRCV
2. Create Journal with the created Journal Receiver: CL-Command CRTJRN
3. Register all physical files and SQL tables in the journal:
CL-Command STRJRNPF
4. Start Commitment Control for your job: CL-Command STRCMTCTL

Birgitta

[Paul J. Therrien]

To question number 2:

Turn journaling on with STRJRNPF. This can be done at anytime and is
permanent. You can stop journalling of a file at any time with
ENDJRNPF.

Paul

[Terry Schwarz]

This seems most promising ... thank you for specific command verbs ... will
have to test.

I gather from Kent's reply, this will automatically pick up any new creates
... that would be great.

Thank you

[Kent Millligan]

Yes, it recognizes new creates.

[Terry Schwarz]

Thank you Birgitta, Kent, Walker and Paul that replied to my original query.
Its been about a week and I finally got to test suggestions.

The CRTJRNRCV/CRTJRN/STRJRNPF/STRCMTCTL method seems to work as advertised,
however, the V5R3 specific CRTDTAARA DTAARA(XXXXX/QDFTJRN) method as
outlined in Flexible SQL Journaling
http://www.mcpressonline.com/mc/.6b3151b7 is giving me problems and
basically is being ignored.

Our app gets a 7905 error code which maps to same error message I get from
STRSQL ...

CREATE TABLE AAA1(SSSS INT)
Table AAA1 in TMS created but could not be journaled.

Even tried qualifying the table name with the library name and same thing.

Is there some kind of system set up thing that should have been done to
activate the QDFTJRN feature from a system perspective before use or
possibly from a user id perspective (I did logoff/on)?

In case I did something wrong in syntax/steps here are explicit commands I
used in attempting to use the journalling of an existing collection...

CHGCURLIB TMS
STRSQL -> CREATE COLLECTION TMSV1
CRTDTAARA DTAARA(TMS/QDFTJRN) TYPE(*CHAR) LEN(25) VALUE('TMSV1 QSQJRN
*FILE')
STRSQL -> CREATE TABLE AAA1(SSSS INT)

I also tried setting up manual journals as in the
CRTJRNRCV/CRTJRN/STRJRNPF/STRCMTCTL method and same behavior.

[Terry Schwarz]

CRTJRNRCV/CRTJRN/STRJRNPF/STRCMTCTL method and then use QDFTJRN to point to
those journals and got same behavior.

[Kent Millligan]

In your example it doesn't look like you used 10 characters for the
library and journal name.

[Terry Schwarz]

I checked a few times immediately after my original post, didn't see
anything and then I haven't been on until now.

Yes blank padding to 10 for file and lib was the problem ... thank you.

"Kent Millligan" <km...@us.eye-b-m.com> wrote in message
news:43ecef75$1@kcnews01...