Get data queried in SQL over to a table on an i Series platform (AS400). I am querying 3 rows and need to populate these rows in a table on an AS400 i Series platform. The table in the AS400 has already been created and has the necessary columns to accept the data from SQL. AS400 table columns = PPRP#1, PPDATE, PPCOUNT
SQL command
select b.new_SalesrepId as PPRP#1, max(CONVERT(varchar(8), a.ScheduledStart, 112)) as PPDATE, count(b.new_SalesrepId) as PPCOUNT from ActivityPointerBase as a
join SystemUserExtensionBase as b on b.SystemUserId = a.OwnerId
where a.ActivityTypeCode = '4201' and b.new_SalesrepId is not NULL and a.StateCode = '1' and a.ScheduledStart <= GetDate()
group by b.new_SalesrepId, CONVERT(varchar(8), a.ScheduledStart, 112)
order by b.new_SalesrepId ASC
This will be a nightly job, so at 11pm SQL will query the records above and then write them to the AS400 table to the proper column.
The only part about this that I am unfamiliar with is how to tell SQL to write the records to the AS400 table?
UPDATE 1: I was able to successfully work through creating a Linked Server in SQL to our i Series box. Some settings have to be enabled in order for the connection to work properly. If anyone would like to know just ask.
UPDATE 2: Ok I got this statement to succesfully run select * from AS400.S062f7ar.APLUS83MDS.PEPAPPTS' but when I try to do an update like:
Update [AS400].S062f7ar.APLUS83MDS.PEPAPPTS
set PPCOUNT = '7'
where PPREP1 = 'FIR00107'
I get a message saying: OLE DB provider "IBMDASQL" for linked server "AS400" returned message "SQL7008: PEPAPPTS in APLUS83MDS not valid for operation. Cause . . . . . : The reason code is 3. Reason codes are: 1 -- PEPAPPTS has no members. 2 -- PEPAPPTS has been saved with storage free. 3 -- PEPAPPTS not journaled, no authority to the journal, or the journal state is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal. Recovery . . . : 3 -- Start journaling on PEPAPPTS (STRJRNPF), get access to the journal, or change th...
Is this something I have to on the i Series file to allow writing to this table?
The table PEPAPPTS is not journaled on DB2 for i. Have the DB2 sysadmin journal the table.
If that is unacceptable for some reason, turn off commitment control in your driver. Use IsolationLevel *NONE (or perhaps Chaos).
For the documentation, see: Start > Programs > IBM i Access > Programmer's Toolkit > OLE DB Provider Technical Reference.