file

[John192]

Hi,

To get all the fields detail of all the libraries and all the files in the system will below command be the best?

DSPFFD FILE(*ALL/*ALL) OUTFILE(*OUTFILE) OUTFILE(L1/t1) OUTMBR(*FIRST *ADD) SYSTEM(*ALL)


However I tried above but after executing above command it started taking too much cpu % , is something wrong I am doing here I mean should I have executed it like below:-

DSPFFD FILE(*ALL/*ALL) OUTFILE(*OUTFILE) OUTFILE(L1/t1) OUTMBR(*FIRST *REPLACE) SYSTEM(*ALL)

?



Thanks much..

[andreas.prouza]

Hi,
There is a SQL View you can use for this.
Select * from QSYS2.SYSCOLUMNS
Where TABLE_SCHEMA = 'PROUZA'

[John192]

this query requires to specify library name in TABLE_SCHEMA but as I am unsure of the field location i mean not sure of it's file name and library name that is why i executed that DSPFFD command but it seems to take lot of CPU% and keeps on running for a long time..


Thanks much...

[John192]

also i found it's not having desired file and field information evenwhen this DSPFFD completed it's execution,why is so?


Thanks..

[andreas.prouza]

You don't need to specify a table_schema in this SQL.
This was just an example from me. It's simple SQL you can specify in your WHERE statement (nearly) what ever you want.

[John192]

but when i tried to find my specific field name by trying this SQL query then I am getting this error:- 'Character conversion between CCSID 1200 and CCSID 65535 not valid'

SELECT * FROM QSYS2.SYSCOLUMNS WHERE COLUMN_NAME= 'my specific field name' or LONG_COMMENT='my specific field name' or COLUMN_HEADING ='my specific field name' OR COLUMN _TEXT='my specific field name' or SYSTEM_COLUMN_NAME ='my specific field name'



Thanks...

[John192]

I even tried %like still same error.

[B.Hauser]

Change your job CCSID from 65535 to 37 ... and then rerun the query again.

[John192]

Change your job CCSID from 65535 to 37 ... and then rerun the query again.

Thanks but how to make these changes and where to make these changes?




Thanks much....

[John192]

Thanks but how to make these changes and where to make these changes?


Also doing so in production environment will make any impact on production jobs?

Thanks much....

[andreas.prouza]

Thanks but how to make these changes and where to make these changes?

CHGJOB CCSID(37)
This affects only your session

[John192]

Hi,

To get all the fields detail of all the libraries and all the files in the system will below command be the best?

DSPFFD FILE(*ALL/*ALL) OUTFILE(*OUTFILE) OUTFILE(L1/t1) OUTMBR(*FIRST *ADD) SYSTEM(*ALL)


However I tried above but after executing above command it started taking too much cpu % , is something wrong I am doing here I mean should I have executed it like below:-

DSPFFD FILE(*ALL/*ALL) OUTFILE(*OUTFILE) OUTFILE(L1/t1) OUTMBR(*FIRST *REPLACE) SYSTEM(*ALL)

?



Thanks much..

*replace or * add which one would have been better for this purpose?


Thanks much....

[andreas.prouza]

I don't know if I understand you correct.
Is it working with the SQL or not?
Did you try to change your job ccsid?
What do you meam with "replace or"?

I would always prefere the SQL solution. It's more comfortable.
If you make the query against the whole system, you need to check if the performance is good enaugh for your or if you need another solution.

Btw, you will not find files in QTEMP in that View.

[John192]

yes, it's working and was able change CCSID of my job also for replace and add query i asked it in terms of this command where we get two options in OUTMBR parameter field to choose between *ADD or * REPLACE from ( DSPFFD FILE(*ALL/*ALL) OUTFILE(*OUTFILE) OUTFILE(L1/t1) OUTMBR(*FIRST *ADD) SYSTEM(*ALL) )

whichone should be chosen here?

secondly what did you mean to say i would not find iles in QTEMP did you mean to say this for DSPFFD or for SQL query :-SELECT * FROM QSYS2.SYSCOLUMNS ?


I just found in above cases DSPFFD was better as in outfile i was able to search specific field name using DSPPFM with the use of SHIFT +F4 then based on record number using SELECT* FROM FILENAME where RRN(FILENAME) ='that RRN Number which i was able to find using SHIFT+F4 in DSPPFM '


Also can we know a particular file is being used by these many programs using this SQL query ? If No, Then how to know it if we have just file name available with us?




Thanks much....

[John192]

I meant I was able to find using SELECT * FROM QSYS2.SYSCOLUMNS also but it was easier to find that specific field name using using DSPPFM with the use of SHIFT +F4 then based on record number using SELECT* FROM FILENAME where RRN(FILENAME) ='that RRN Number which i was able to find using SHIFT+F4 in DSPPFM ' .


Thanks much...

[John192]

also one more reason i found SQL SELECT * FROM QSYS2.SYSCOLUMNS more difficult to find the specific field name because it has too many column with fields so whichone to choose from ?

whereas in DSPFFD outtfile we can simply do DSPPFM and then do Shift + F4 to find the corresponding record number for that field and then based on that record number we can use SQL query like i mentioned in my previous post like SELECT* FROM FILENAME where RRN(FILENAME) ='that RRN Number which i was able to find using SHIFT+F4 in DSPPFM ' .




Thanks much..

[andreas.prouza]

If you feel more comfortable with DSPFFD, then go ahead with it.
When you redirect the output into an outfile, you can either choos to *ADD (the outfile will not be cleared and the output will just appand to it) or *REPLACE (the content will be deleted bevor the data is added to it).
Usually you want to *REPLACE it. Otherwise you will have redundant data.

In both, the SQL View and also in the OUTFILE you have many columns.
At least for the SQL View you have a good documentation in the IBM i SQL Reference (google).
But as I see you are not familiar with SQL, because if you want to search for a specific field, you don't use SHIFT+F4, you use the WHERE clausl in the SQL Statement.