The CREATE COLLECTION statement is a synonym for CREATE SCHEMA. The
effect of CREATE SCHEMA SchemaName is essentially:
crtlib SchemaName /* aut according to NAMING option */
crtjrnrcv SchemaName/QSQJRN0001 /* maybe custom THRESHOLD */
crtjrn SchemaName/QSQJRN JRNRCV(SchemaName/QSQJRN0001)
call qsqxrlf (crt SchemaName) /* create custom catalog VIEWs */
The SQL catalog VIEW objects [SYSVIEWS, SYSTABLES, SYSCOLUMNS, et al]
created into the named SCHEMA are customized to effect WHERE clause to
SELECT only those object in that particular SCHEMA; i.e. WHERE
DBCCNL='SCHEMANAME' /* would be part of the VIEW definition for the
catalog view named SYSREFCST */
Because these VIEWs are redundant [available in QSYS2 and\or SYSIBM,
without the custom selection on a Schema name], I will generally delete
them [using CALL QSQXRLF (DLT SchemaName)], but unfortunately any VIEWs
that are added to a CREATE COLLECTION since what was being added in
v2r1m1, the action of adding an object that would be tracked to any
particular catalog VIEW effects the creation of those VIEWs. Maybe that
is no longer true in the latest release(s), but no matter how many times
I had complained [even when that work caused /problems/ such as with
save and restore], that _feature_ remained :-(
Of course with regard to the other quoted messages...
A CREATE COLLECTION [SCHEMA] can not be used for an existing
library name. Thus use of the CRTJRN or effecting implicit\default
journaling is easiest\based for an existing library, and STRJRNPF for
any existing TABLE, as I had noted in:
http://archive.midrange.com/midrange-l/201206/msg00179.html
Journaling is not required when using COMMIT(*NONE), irrespective
of whence the SQL was issued. The isolation can be specified scoped to
the statement using a WITH-isolation clause, if the connection either
specifies or defaults to an isolation which does not effect
CmtLvl\isolation of *NC [COMMIT(*NONE)]:
http://archive.midrange.com/midrange-l/201206/msg00173.html
_i CREATE SCHEMA i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzxcschema.htm
"CREATE SCHEMA
The CREATE SCHEMA statement defines a schema at the current server and
optionally creates tables, views, aliases, indexes, and distinct types. ...
Notes
Schema attributes: A schema is created as:
* A library: A library groups related objects, and allows you to
find objects by name.
* A catalog: A catalog contains descriptions of the tables, views,
indexes, and packages in the schema. A catalog consists of a set of
views. For more information, see SQL Programming.
* A journal and journal receiver: A journal QSQJRN and journal
receiver QSQJRN0001 is created in the schema, and is used to record
changes to all tables subsequently created in the schema. For more
information, see Journal Management.
...
Syntax alternatives: The COLLECTION keyword can be used as a synonym for
SCHEMA for compatibility to prior releases. This keyword is non-standard
and should not be used.
...
"
Regards, Chuck
On 07 Jun 2012 13:48, John McKee wrote:
That is interesting. I did that just to see what was created. A
journal, a journal receiver, and a bunch of logical files. But, no
physical files. DSPDBR doesn't show a physical file. What is
happening there?
On Thu, Jun 7, 2012 at 3:18 PM, Alan Campin wrote:
Easiest way is to create an SQL library. Do a STRSQL and type a
CREATE COLLECTION and enter a library name. It will create a
library with journal and all SQL objects. Any table that is created
in it is automatically journaled.
On Thu, Jun 7, 2012 at 2:13 PM, Brian Piotrowski wrote:
If journaling needs to be turned on, how do I do it through a
create table statement in the interpreter?
Alan Campin on Thursday, June 07, 2012 3:19 PM wrote:
The table must be journaled to write records to it from SQL
Server or any PC platform.
On Thu, Jun 7, 2012 at 12:39 PM, Brian Piotrowski wrote:
I created a table in the SQL Interpreter using this statement:
CREATE TABLE SPSL/PLC_WORK2 (PLANTCODE CHAR ( 1) NOT NULL
WITH DEFAULT, LREFNUM INT NOT NULL WITH DEFAULT, MDLCODE INT
NOT NULL WITH DEFAULT, ACK INT , MTOC CHAR ( 6) NOT NULL WITH
DEFAULT)
(As seen above, the "ACK" field is set to null when it is
not filled in - this is a requirement from the PLC system).
I received this message back:
Table PLC_WORK2 in SPSL created but was not journaled.
However, now when I try to update the table through another
SQL statement (it's actually a scheduled task running on our
SQL2008 server) I'm getting a CPF4328 error.
How do I manually update the SQL table so that I can have the
SQL statement update the table without it throwing the
CPF4328 error? I know it has something to do with the
commitment control being set to *NC, but I don't know how to
set it on the created table.
Any ideas?
midrange.com
