ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL changes member name?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL changes member name?

    We are in the process of working our way out of S36 enviromnent with RPGii and flat files. (Don't ask).

    We had a flat file that has 2 record types. One is data we need, the other is free form text data that overlays numeric data on the first record type.


    We have created an external definition and copied all the data into it. If the record code is 1, we have valid data. If the record code is 2, we ignore the record because it is putting text data into numeric fields.

    When using SQL, as long as we seleted record type 1 first, SQL works correctly. (if we accidentally include type 2, we get invalid data errors).


    We are using embedded SQL and today we got invalid data errors. We changed the order of the selects so that record type =1 is first and that fixed the problem.

    But somehow, SQL changed the member name to M181130. (today's date) Well, we're pretty sure it's SQL as we have no other processing that would change the member name.

    Has anyone run accross SQL changing a member name? Or are we completly off base here and the member name is being changed somewhere else?


    And yes, I am aware that having 2 records types in one externally defined file does not work well with DDS and SQL. But I am doing a delicate dance here to to keep things running while we move from the stone age to the bronze age, let alone the modern age.

  • #2
    SQL doesn't really have any notion of members, so I find it extremely unlikely that it would change a member name.

    It has been a VERY long time since I've worked with S/36 stuff, but I seem to recall that member names like M181130 (M followed by a date) are generated by the S/36 tools. So if you have a member with a name like that, it's likely coming from a S/36 tool. I want to say the tool was named something like BLDFILE or BLDINDEX but I don't really remember that much detail. SQL would never create a member with a name like that, even if it did do something with members (which, again, I think is extremely unlikely)

    You didn't really ask this, but: IMHO, writing SQL that only reads certain "record types" is not a good long-term solution. Instead, you should separate each record type into a separate table (aka physical file) so that you have only one type of recorrd in each table. It's possible to make a multi-format logical file for backwards compatability for the S/36 programs, so that they have one file to work with, but under the covers the data is split into multiple files, one per record. Your SQL code (and even RPG code that was written after the mid 1980s) will be a lot easier/nicer to work with when you don't have these multiple record types of files.

    Comment


    • #3
      Thanks for confirming what I suspected, SQL leaves member names alone. I will review our processing and see if the file is being recreated with BLDFILE, which is entirely possible.

      I agree with you that "record types" are not practical or desirable, but having an external definition over the part of the file we need is much better than nothing. I have created two tables with a join logical in our DEV partition and a record selector program, but I need to be very cautious, as this is a main file driving the business.

      Comment


      • #4
        Just to be clear: A multi-format logical is the thing that works like a S/36 file with multiple record times. A join logical works differently. You might've said "join logical" by mistake, but I thought I'd post this just in case.

        Comment


        • #5
          Yes, I meant to say mulit format logical.

          Comment

          Working...
          X