Massive bulk AS/400

Is there a way to make a massive bulk for AS/400? For example: I have 5000 SKU's that we need to change price or cost. Right now, we do it manually. Is there a better and efficient way to do it?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 8  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • azohawk

    it depends.  Is there are rule  such as: increase all item cost by 3% or increase items that have an item class or 'X' by $1 or other similar  rule(s)?  If so you can write a simple program or SQL statement to update the file.  if you have several sets of rules, you might want to do it in various steps.

    Is there is no rule, but you have the new pricing in a spreadsheet or delimited text file, you can upload that file, the write a program or SQL to update the pricing from the uploaded file.

    4,055 pointsBadges:

    Thanks for the quick response, could you please be more specific about uploading the file please... I`m not so familiar with SQL, so let me see if a I full understood.

    1-Any spreadsheet from excel can be uploaded to AS400?

    2-This upload is directly from the option receive files from host?

    3-Is there an specific screen where the massive bulks have to be uploaded.

    Thanks azohawk




    25 pointsBadges:
  • azohawk

    I have had a  few occassions that saving as a delimited text file works better but that only makes a difference in the file type you select.

    In Client Access-->Actions-->Send file to host.  (There is an icon for it, but this works as well).  Pop up window, Tools-->Create database file. Follow the prompts. Select the spreadsheet, then the file type, the name of the FDF file you will be creating, data options (allows you to determine how date, time and numeric data appears and some scan defaults as part of the data conversion->scan the data, make changes to how the fields are defined (change from the default name, change field size, etc.) When that entire process is done the new file will be created and you will be prompted to upload the file.

    Since you don't know SQL, and I'm not strong enough in that my self to try and give you a quick approach.  A simple RPG program would read your newfile one record at a time.

    fnewfile      if e disk

    fcurrentfile  uf e k disk

    //current file keyed by item no.

    fprinterfile   o e printer oflind(ovrflo)

    //printer file is optional, but I use so I can review my updates

    d ovrflo s n inz(*on)


    read newfile;

    dou %eof(newfile);

    chain (partno.from.newfile) currentfile;

    If not %found(currentfile);

    NFound += 1;  //report field counter

    eFlag = ' *';     //report field

    status = 'Item not found in masterfile';  //report field



    update(e) %Fields(price.field);

    if %error;

    error +=1; //report counter field

    Status = 'error on update of itcls';

    eflag = ' ***';


    good += 1; //report counter field

    status = 'Update successful';

    eflag = *blank;

    endif; //error

    endif; //not found

    exsr srreport;  // populates field for my report and prints it.

    read newfile; //reads the next record of the file


    write foot;

    *inlr= *on;


    begsr srreport;

    //move fields to appropriate report fields

    if ovrflo;

    page# +=1;

    write head;

    ovrflo = *off;


    total +=1;

    write detail;

    status = *blank;

    eflag = *blank;


    This is a program I wrote yesterday that is similar to what you will be doing with your program. Note, some of the field and file names are generic here and the formatting didn't stay, but you should be able to work around that.  The new file has to have whatever will uniquely identify each record and the new price.  (i.e. item# or item# and vendor or customer, etc.)

    4,055 pointsBadges:

    Thanks, I can see you full understand the system as400, I`ll be making some test this week in order to dominate this issue.

    On the other hand I have an easier issue, I was told to change some sku`s with 30% discount, I have just a few today but soon I`ll have a list with 1000 skus, is it the same case? or a faster way due they chang equally in 30%.


    Id Sku %
    556813 30%
    561538 30%
    561537 30%
    551239 30%
    556504 30%
    549843 30%
    556784 30%
    552941 30%
    556759 30%
    556956 30%
    556958 30%




    25 pointsBadges:
  • ToddN2000

    You can use the Client Access Excel add-in to transfer data to the AS/400. You can create a LIB on the AS/400 and name it something like "uploads".

    Upload the Excell file using the add-in to this library. I have much better luck with .CSV file formats.

    Then you can write a simple program to update your pricing file

    133,645 pointsBadges:
  • barrygiles64

    If you are talking SKU's on an AS400 sounds like you are using JDA's MMS or E3. I have clients using these systems and have lots of utilities do do what you want. Let me know actually what you are tying to do and I could already have some programs or SQL's that do this. I have a client that does this via Client Access Macros where the SKU list is generated from an Excel that gets created using Mail merge in word for the client access macro.

    60 pointsBadges:
  • azohawk
    If there is not methodology to the skus, you could load them seperatly and write a similar program that would  price *=1.3  for the increase. Or put them in the same file that you upload with a seperate field, modify the program if fieldx = zero  price = newprice else price *= (1 + (fieldx/100).
    4,055 pointsBadges:
  • agasca
    Some assumptions for Massive bulk to AS400: There is no way to upload massive bulk to as400 prior some procedures regarding database integrity and creation of DML update queries and TCL rollback, therefore, the easy way is to ask the DBA at the IT department about your request, show them the screens where data has to be uploaded.
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: