Topic
  • 16 replies
  • Latest Post - ‏2014-07-30T08:07:59Z by mkoni
mkoni
mkoni
39 Posts

Pinned topic V7R1 TR8 GENSQL

‏2014-06-11T09:30:36Z |

I've installed TR8 for V7R1 on iSeries and iI did some tests for the new SQL procedure GENERATE_SQL.

GENERATE_SQL works as described, I put the sql-source in the file Q_GENSQL.

If I do a CPYTOSTMF from the database file to a streamfile (pcfile) in the IFS. I need it because I work with "run sql scripts" from iNav.

The stramfile has a blank line after each line (each second line is a blank line). I tried all possible values for ENDLINFMT, but did not help.

What can I do to avoid the blank lines (in the database source file are not blank lines)

  • ScottForstie
    ScottForstie
    12 Posts
    ACCEPTED ANSWER

    Re: V7R1 TR8 GENSQL

    ‏2014-07-28T17:43:32Z  
    • mkoni
    • ‏2014-07-28T08:06:01Z

    Hi Scott,

    I was on vacation, sorry for the delay.

    your point of view is clear for me, I can accept this.

    to 1.) I use CPYTOSTMF. Is it better to use CPYTOIMPF ? where are the difference in this case ?

    to 2.) I think it would be better to have an option in the GENSQL procedure to omit the control characters (CR/LF)

     

    regards mike

    Both commands have been around for a long time, but CPYTOSTMF was created first.  The net is that CPYTOIMPF was specifically created for the task of copying DB2 for i data to an IFS stream file.

    We continue to provide more flexibility for CPYTOIMPF (see below).  Also, CPYTOIMPF honors overrides and has other command level controls not found on CPYTOSTMF.

    We'll discuss #2 as we review our plans.
     

    For example:

     

  • B.Hauser
    B.Hauser
    280 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-11T12:59:10Z  

    This may not be an answer to your question, but you don't need to store the generated SQL script within the IFS.

    With RUN AN SQL SCRIPT you can open and execute SQL sources located in physical file members.

    Birgitta

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-11T14:16:05Z  
    • B.Hauser
    • ‏2014-06-11T12:59:10Z

    This may not be an answer to your question, but you don't need to store the generated SQL script within the IFS.

    With RUN AN SQL SCRIPT you can open and execute SQL sources located in physical file members.

    Birgitta

    Brigitta, thx for your answer

    I know your suggestion, but I need the sql-scripts in a pc-file. all works fine except the blank lines after calling CPYTOSTMF.

    is there a way to avoid blank lines on each second line (because there are no blank lines in db-member).

    as described above, I tried all possible values for ENDLINFMT with no success.

     

    in iNav if you use 'generate SQL' you can choose to store the output in a db-member or in a pc-file. in the new GENERATE_SQL procedure I miss this option.

     

    maybe this is not the right forum for CPYTOSTMF (I've this problem only with GENERATE_SQL members) - if so, pls let me know or move it.

     

    mike

    Updated on 2014-06-11T14:22:52Z at 2014-06-11T14:22:52Z by mkoni
  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-13T09:01:22Z  
    • mkoni
    • ‏2014-06-11T14:16:05Z

    Brigitta, thx for your answer

    I know your suggestion, but I need the sql-scripts in a pc-file. all works fine except the blank lines after calling CPYTOSTMF.

    is there a way to avoid blank lines on each second line (because there are no blank lines in db-member).

    as described above, I tried all possible values for ENDLINFMT with no success.

     

    in iNav if you use 'generate SQL' you can choose to store the output in a db-member or in a pc-file. in the new GENERATE_SQL procedure I miss this option.

     

    maybe this is not the right forum for CPYTOSTMF (I've this problem only with GENERATE_SQL members) - if so, pls let me know or move it.

     

    mike

    I solved the problem.

    After generating the SQL-script in the db-source file I did an update to the db to remove the last two chacters (which is CR and LF).

    After this all works fine.

    Updated on 2014-06-13T09:01:43Z at 2014-06-13T09:01:43Z by mkoni
  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-16T09:22:14Z  
    • B.Hauser
    • ‏2014-06-11T12:59:10Z

    This may not be an answer to your question, but you don't need to store the generated SQL script within the IFS.

    With RUN AN SQL SCRIPT you can open and execute SQL sources located in physical file members.

    Birgitta

    I've an additional question.

    I miss in the GENERATE SQL the option to save it as PC-File in the IFS.

    In iNav there is an option to save it as PC-File or in a database, the GENERATE SQL procedure provide only to save it in a database..

    Would this added in a further release ?

    Updated on 2014-06-17T07:10:03Z at 2014-06-17T07:10:03Z by mkoni
  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-23T06:45:28Z  
    • mkoni
    • ‏2014-06-16T09:22:14Z

    I've an additional question.

    I miss in the GENERATE SQL the option to save it as PC-File in the IFS.

    In iNav there is an option to save it as PC-File or in a database, the GENERATE SQL procedure provide only to save it in a database..

    Would this added in a further release ?

    Hi, did anyone read my question above ?

  • ScottForstie
    ScottForstie
    12 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-24T15:01:03Z  

    Hello Michael,

    Would you please email me (at forstie@us.ibm.com) a description of why you want the procedure to write the output to a IFS stream file?

    Help us understand your use case / scenario a little better.

    Thanks.

    Scott

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-06-30T06:32:30Z  

    Hello Michael,

    Would you please email me (at forstie@us.ibm.com) a description of why you want the procedure to write the output to a IFS stream file?

    Help us understand your use case / scenario a little better.

    Thanks.

    Scott

    Hi Scott,

    I sent you an email last week. did you get it ?

    mike

  • tomliotta
    tomliotta
    49 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-01T04:39:56Z  

    Hello Michael,

    Would you please email me (at forstie@us.ibm.com) a description of why you want the procedure to write the output to a IFS stream file?

    Help us understand your use case / scenario a little better.

    Thanks.

    Scott

    It seems a little odd that characters such as CR/LF get included for anything not intended for a streamfile. I'd be interested in a use-case that needed them. I've also gone through processes of removing apparently extraneous end-of-line characters from source file members and wondered why they were there. They made a little sense in a process that allows a choice of streamfile or source member, so I didn't over-think it.

    Tom

     

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-03T09:35:18Z  

    Hello Michael,

    Would you please email me (at forstie@us.ibm.com) a description of why you want the procedure to write the output to a IFS stream file?

    Help us understand your use case / scenario a little better.

    Thanks.

    Scott

    Hello Scott,

    I've sent you an Email as you wanted, I hope you got it.

    I've no answer till now.  Pls give me feedback.

    mike

    Updated on 2014-07-03T09:35:38Z at 2014-07-03T09:35:38Z by mkoni
  • krmilligan
    krmilligan
    450 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-03T14:26:57Z  
    • mkoni
    • ‏2014-07-03T09:35:18Z

    Hello Scott,

    I've sent you an Email as you wanted, I hope you got it.

    I've no answer till now.  Pls give me feedback.

    mike

    I know Scott is on vacation this week.

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-04T06:17:42Z  

    I know Scott is on vacation this week.

    thx for information

    mike

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-20T11:55:32Z  

    Hello Michael,

    Would you please email me (at forstie@us.ibm.com) a description of why you want the procedure to write the output to a IFS stream file?

    Help us understand your use case / scenario a little better.

    Thanks.

    Scott

    Hi Scott,

    are you still on vacation ? - did you get my email last month ?

  • ScottForstie
    ScottForstie
    12 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-21T13:22:21Z  
    • mkoni
    • ‏2014-07-20T11:55:32Z

    Hi Scott,

    are you still on vacation ? - did you get my email last month ?

    Hello again,
    Sorry for the delay in replying, but July is a patchwork of vacations and filling in for colleagues who go on vacation.

    We have discussed your scenario and have decided on a couple things:
    1) We won't be adding support to GENERATE_SQL for an IFS target.  While we could add such an option, you have a way to do this today in two steps using the CPYTOIMPF command.  That copy command has many options of its own and we envision we would need to continue to adjust GENERATE_SQL to provide equivalent function.  Therefore, we're keeping GENERATE_SQL focused on generation control and CPYTOIMPF focused on copy control.

    2) On the generate, we intentionally preserve the control characters in the routine body to maintain formatting and readability.  I realize that what you've observed is less readable than we'd like.  We will consider enhancing CPYTOIMPF to allow no record delimiter. This option would improve the readability of the source.  I don't have a target in mind for this enhancement.

    Regards, Scott

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-28T08:06:01Z  

    Hello again,
    Sorry for the delay in replying, but July is a patchwork of vacations and filling in for colleagues who go on vacation.

    We have discussed your scenario and have decided on a couple things:
    1) We won't be adding support to GENERATE_SQL for an IFS target.  While we could add such an option, you have a way to do this today in two steps using the CPYTOIMPF command.  That copy command has many options of its own and we envision we would need to continue to adjust GENERATE_SQL to provide equivalent function.  Therefore, we're keeping GENERATE_SQL focused on generation control and CPYTOIMPF focused on copy control.

    2) On the generate, we intentionally preserve the control characters in the routine body to maintain formatting and readability.  I realize that what you've observed is less readable than we'd like.  We will consider enhancing CPYTOIMPF to allow no record delimiter. This option would improve the readability of the source.  I don't have a target in mind for this enhancement.

    Regards, Scott

    Hi Scott,

    I was on vacation, sorry for the delay.

    your point of view is clear for me, I can accept this.

    to 1.) I use CPYTOSTMF. Is it better to use CPYTOIMPF ? where are the difference in this case ?

    to 2.) I think it would be better to have an option in the GENSQL procedure to omit the control characters (CR/LF)

     

    regards mike

    Updated on 2014-07-28T08:06:40Z at 2014-07-28T08:06:40Z by mkoni
  • ScottForstie
    ScottForstie
    12 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-28T17:43:32Z  
    • mkoni
    • ‏2014-07-28T08:06:01Z

    Hi Scott,

    I was on vacation, sorry for the delay.

    your point of view is clear for me, I can accept this.

    to 1.) I use CPYTOSTMF. Is it better to use CPYTOIMPF ? where are the difference in this case ?

    to 2.) I think it would be better to have an option in the GENSQL procedure to omit the control characters (CR/LF)

     

    regards mike

    Both commands have been around for a long time, but CPYTOSTMF was created first.  The net is that CPYTOIMPF was specifically created for the task of copying DB2 for i data to an IFS stream file.

    We continue to provide more flexibility for CPYTOIMPF (see below).  Also, CPYTOIMPF honors overrides and has other command level controls not found on CPYTOSTMF.

    We'll discuss #2 as we review our plans.
     

    For example:

     

  • mkoni
    mkoni
    39 Posts

    Re: V7R1 TR8 GENSQL

    ‏2014-07-30T08:07:59Z  

    Both commands have been around for a long time, but CPYTOSTMF was created first.  The net is that CPYTOIMPF was specifically created for the task of copying DB2 for i data to an IFS stream file.

    We continue to provide more flexibility for CPYTOIMPF (see below).  Also, CPYTOIMPF honors overrides and has other command level controls not found on CPYTOSTMF.

    We'll discuss #2 as we review our plans.
     

    For example:

     

    Hi Scott,

    thx for yiur answer.