On 15-Jul-2016 08:50 -0500, John Cirocco wrote:
Sorry, But I have not been on an AS400/i-Series/System-i since
2007... After having managed for almost 20 years I really thought
this would be easy...
FWiW, I seem to recall they were:
AS/400 with OS/400
iSeries with OS/400 or i5/OS
System i with i5/OS or IBM i
And most recently: IBM Power Systems with IBM i
A friend asked me to move a ton (well over 2000) Physical Files from
a couple of /400's, that are going away, to some place that he can
access to import to SQL Server.
Given use of that moniker, the AS/400, quite possibly the version
release and modification (VRM) level from which the data must be
exported, would be too old for use of Recursive Common Table Expression
(RCTE or Recursive CTE) which are not supported until V5R4. What is the
V#R# [M# being mostly optional] of the OS?
I tried CPYTOIMPF but does not include the Field Names in the
resulting CSV. I would rather NOT to a manual file transfer of every
file.
As others have suggested, the capability was added to that database
Export feature, the Copy To Import File (CPYTOIMPF), with a new Add
Column Names (ADDCOLNAM) parameter, offering special values *NONE as the
default [so as to continue operating as in the past, with no column
names included in the exported data], *SQL to denote that the DDS ALIAS
names aka /long-names/ will be included, and *SYS to denote that the
standard DDS aka /short-names/ will be included.
Even willing to look at free tools/utilities.
Thanks in advance for the help.
A variation on the following [here I query a non-VIEW LF, but
SYSCOLUMNS or similar] could be used easily enough to generate the
varying-length [currently limited to 5000 chars] character string of
optionally delimited field names for any particular qualified DataBase
File [:DBFLIBNAME as the library name in 10-byte\system naming and the
:DBFILENAME as the file name in 10-byte\system naming]:
with
fldlstrow (dbifld, dbipos) as
( select rtrim(dbifld), dbipos
from qsys.qadbiatr
where dbilib=:DBFLIBNAME
and dbifil=:DBFILENAME
and dbirel='Y' and dbifmp=1 )
,fldlstcol (fldlst, dbipos) as
( select dbifld, dbipos
from fldlstrow where dbipos=1
union all
select varchar(fldlst concat ', ' concat r.dbifld, 5000)
, c.dbipos+1
from fldlstcol c, fldlstrow r
where c.dbipos+1 = r.dbipos )
select fldlst
from fldlstcol
where dbipos = (select max(dbipos) from fldlstrow)
midrange.com
