Comparing ISeries Utilities to SAP

Well I thought I would go back to my roots a little to show people coming off the ISeries/AS400 etc how you do the same things you have been doing forever.

Lets Start with some simple stuff.
WRKQRY (One my personal Favs) In SAP there is a whole query engine but for most things all you need is SE16N Data Browser wonderful utility there are hundreds of pages on the utility so I am not going to explain it smarter people then me already have.

WRKACTJOB There isnt an exact replacement to this but SM37 comes about as close as I have found.

This is the initial screen from here you can choose to see only active jobs it will also allow you to view jobs that are scheduled and those that have finished. Play with it's a great utility. It does many of the functions that were several commands on the ISeries WRKJOBSCDE WRKJOB

In the ISeries world if you have been around as long as I have been you probally still use SEU to create DDS Specs. The replacement to that is SE11 in SAP.
From this screen you can generate simple data entry screen much like STRDFU Again these are all deeper discussion then I want to get into tonight I am a little to tired.
To run one of these DFU programs you will use Transaction SM30
WRKSPLF is SP01
Now then what about SEU well a simple editor is SE38.
But if your like me you always used STRPDM well SAP has something a whole lot better SE80 comparing the two really cannot be done PDM is just not as good take my word for it. It incorporates STRSDA, STRDBG, DSPPFM, and many others functions that I cannot even think of at this point.
STRRLU There are three replacements for this program in SAP SAP Script, SmartForms, and Adobe Interactive Forms. Books have been written on these subjects and I do hope to do a blog on two of them in the future take my word for it they are awesome. I know I am not being fair WDSC is great and very powerfull and is a greate replacement to the green screen utilites. But during my 20 plus years of doing AS/400 work mostly I have seen programmers only using green screen. The shop I work in only started using WDSC in the past 4 years and in most cases most programmers still go back to the old green screen when they have a quick fix to do.

Just so you don't think I am bashing the ISeries I still think it runs circles around SAP in speed and dependability. The speed that you can create code even though SAP has better tools and more powerfull tools I still think a decent ISeries guy can generate a program quicker then some one who is at the same skill level in SAP. But having said that I personally would still perfer to code in SAP. I love doing new and different stuff which SAP offers in Spades.

Well I think that is enough for tonight I hope to write a few more articles along these lines if you would like to know how to do something you have been doing on the ISeries drop me a line and I will be happy to share what little knowledge I have on the subject.

Last Used Log

My company has been on SAP for 2 years now and some of the programs we wrote two years ago are no longer being used. But because of one reason or another we do not have a good list of what programs are no longer being used. So I asked the basis guy if he could tell me what programs have not been used in the last year. He kindly did a Google search and came back with this link http://www.sapfans.com/forums/viewtopic.php?p=262634. So after reading this forum I deceided to create a log file with a function module that I will inject into the programs that I think are no longer being used and wait for a couple of months then get rid of them. So I decided since I have not posted anything for awhile I would post my function module.

First the Table.

Function Module.
ZPROGRAM_START
Importing Program_Name
Exporting TimeStamp

data: local_time_stamp type timestamp.
  tables zlast_run.
  zlast_run-ran_by = sy-uname.
  zlast_run-program_name = program_name.
  get time stamp field local_time_stamp.
  zlast_run-start_date = local_time_stamp.
  insert zlast_run.
  time_stamp = local_time_stamp.
endfunction.

The function module returns the time so that you can update with the closeing time.
ZPROGRAM_END
Importing Program_Name
Start_Date

data: local_time_stamp       type timestamp.
  get time stamp field local_time_stamp.
  update zlast_run set complete_date = local_time_stamp
        where program_name = program_name
          and start_date   = start_date
          and ran_by       = sy-uname.
endfunction.

At the start of your program insert the call for ZPROGRAM_START make sure you place it after your selection screen section.
Last thing in your code should be the call to ZPROGRAM_END.

If you have any questions or comments drop me a line thanks.

Reading and Writing External Data.

It's been a fun couple of weeks for me short stay in the hospital my appendix decided that it had had enough and wanted to go somewhere else.

When I left work for my little stay in the hospital my company was installing enhancement pack 4 and after a week they were still having problems so they decided to back out the enhancement pack.

I would love to hear how other companies handle this process it just seemed to me to be a total nightmare. The entire development machine was down for a week.

When we started this process I was in the middle of writing a program that was going to read data from another ISeries and then update the data.
I was having an issue with the update and I must have updated my entry in DBCO and somehow I removed the trailing ";" from Connection Info which is not a good idea. For the next few day's I tried to get my connecting to work again without much help I contacted SAP and they played with it for about two days before they came back with "place a trailing ;" and all of a second everything works again.

I thought I would share the native SQL code I used to Delete/Update records on a remote ISeries.

First the Delete

* Check to see if connection is active  
  EXEC SQL.
    SET CONNECTION :con_name
  ENDEXEC.
  if sy-subrc <> 0.
* Connection not yet opened.
    EXEC SQL.
      CONNECT TO :con_name
    ENDEXEC.
  endif.
* Perform the delete nothing magical here just remember 
* to place a ":" in front of your abap variables.
  EXEC SQL.
    DELETE from svvndxp where company            = :local_company
                          and hs_vendor_number   = :local_hs
                          and sap_account_number = :local_sap
  ENDEXEC.
* Don't forget to Commit
  EXEC SQL.
    COMMIT
  ENDEXEC.
* I have been dropping my connection after all updates
* you don't need to I am sure it slows the program down 
* but I felt like being lazy.
  EXEC SQL.
    DISCONNECT :con_name
  ENDEXEC.

Now for the update.

* Same as with the delete check to see if the connection is open if not
* open it.  
  EXEC SQL.
    SET CONNECTION :con_name
  ENDEXEC.
  if sy-subrc <> 0.
    EXEC SQL.
      CONNECT TO :con_name
    ENDEXEC.
  endif.

* Update the data file.
* I'm using an ALV grid to display the records and I have editing turned
* on so I am using hidden fields on the grid to store the original value.
  local_hs       = global_svvndxp-hs_vendor_number.
  local_company  = global_svvndxp-company.
  local_sap      = global_svvndxp-sap_account_number.
  local_hcompany = global_svvndxp-hidden_company.
  local_hhs      = global_svvndxp-hidden_hs_vendor_number.
  local_hsap     = global_svvndxp-hidden_sap_account_number.
  EXEC SQL.
    update svvndxp set COMPANY            = :local_company,
                       HS_VENDOR_NUMBER   = :local_hs,
                       SAP_ACCOUNT_NUMBER = :local_sap
           where COMPANY            = :local_hcompany
             and HS_VENDOR_NUMBER   = :local_hhs
             and SAP_ACCOUNT_NUMBER = :local_hsap
  ENDEXEC.
* Commit transaction
  EXEC SQL.
    COMMIT
  ENDEXEC.
  EXEC SQL.
    DISCONNECT :con_name
  ENDEXEC. 

Well that's it again if you would like the whole program let me know and I would be happy to send it to you.

Reading Data External to SAP

The company I work for converted AP, AR and Payroll last year to SAP we still have the bulk of are data in the ISeries.  We do all of our ordering from the ISeries but all Vendor maintence is done in SAP.  So we have a cross reference file located in the ISeries but AP no longer uses the ISeries so sometimes the cross reference file gets out of sync with SAP.  So I have been doing manual entry as needed the ISeries but have been wanting AP to take over this work.  So I started looking around for  away of allowing SAP to update a table on another system.  In my research I came across some OSS notes which kind of pointed the way so I thought I would share what I found with anyone who might want to do this same thing.

First let me give you some OSS Notes to read 146624, 323151 there are others but these will point the way.

Next I need to tell you that currently were running SAP on an ISeries if your not running on an ISeries you will need to load some additional software on your system.  See the above mentioned OSS notes for further details.

OK enough prelim let's get down to work.
First you have to tell SAP what system you want to intergrate with this is done thru transaction DBCO.
Create a new entry and you should see something like the following screen.

Here is my explanation of the parameters:
DB Connection this is where you will give it a name can be whatever you want.
DBMS  I'm connecting to an ISeries so the value is DB4.
User Name give it a user name whose password does not change all of the time plus has access to the data library.
DB password key in the password twice.
Conn. info You must enter AS4_HOST= then enter system name of your ISeries that your attempting to access this is where I ran into a bit of a problem I had to ask my Basis guy for help to get the correct name you must seperate the two parameters with a ";" the second parameter is AS4_DB_LIBRARY= this is the data library where the file/files are located that you want to pull into your ABAP program.  After you enter the data and save it you will need to start a listener job on the target ISeries. Enter the following command
"STRTCPSVR SERVER(*EDRSQL)".  Now if you want to test this configuration run the following ABAP program adbc_test_connection.  If everything works then were ready to move forward if not go and review the OSS notes I listed above.


The following is some code to read an external database file.
First I am creating an internal table to hold the results.

data:
  begin of global_svvndxp_tab occurs 0,
    company(3)             type c,
    hs_vendor_number(10)   type c,
    sap_account_number(10) type c,
  end of global_svvndxp_tab.

This is a routine I copied from the notes.

* Test if connection 'con_name' has already been opened
  data: con_name like dbcon-con_name.
  con_name = 'ISERIES'.
  EXEC SQL.
    SET CONNECTION :con_name
  ENDEXEC.
  if sy-subrc <> 0.
*   Connection not yet opened.
    EXEC SQL.
      CONNECT TO :con_name
    ENDEXEC.
    if sy-subrc <> 0.
*     error handling
    endif.
  endif.

* Execute a SQL statement on remote ISeries.
  EXEC SQL performing loop_output.
    select COMPANY,
           HS_VENDOR_NUMBER,
           SAP_ACCOUNT_NUMBER from svvndxp into :global_svvndxp_tab
            where SAP_CLIENT = '400'
  ENDEXEC.
    EXEC SQL.
      DISCONNECT :con_name
    ENDEXEC.
* Reset to "default connection"
  EXEC SQL.
    SET CONNECTION DEFAULT
  ENDEXEC.

endform.

form loop_output.
  write: / global_svvndxp_tab-company, global_svvndxp_tab-hs_vendor_number,
           global_svvndxp_tab.
endform.

Well that's as far as I have gone with this so far I will be loading the data into an ALV Grid allowing the users to edit delete and add records.

I hope this gives you some ideas and some help in setting it up drop me a message if you need any help.

I found this on the web seems like it could be of use.

SAP ABAP Developer $96,000

View Larger Salary Graph

Progress Indicator

Sometimes you create a long running application that runs interactive. The user runs the application and it chugs away and the user starts to think something is wrong and they end there session. When all that is really happening is the program has a lot of data to go through. So what we need is the ability to notify the user that the program is still running. Well lucky for us someone at SAP has taken care of the issue for us.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
  EXPORTING 
    percentage = sy-index 
    text = percentage_text.

What this function does is to place a small stop watch and some text at the bottom of the screen.

Text could be anything If you choose to use a percent complete your program will need to determine how complete you are.

Excel to ABAP

It's cold here today were getting the first snow of the season. I am having a diffuculty coming up with anything to write about besides the fact that I hate snow. I know what your think why for heaven sake do you live in Conklin, Ny then. Well I moved up here in 2004 to do a project and I ended up getting involved with a women here who has the two greatest daughters in the world. So now I can't leave at least for a few years. :> But I can't complain to much it's given me a chance to get involved in this SAP project which has been great.

Ok now that you know more about me then you should lets get down to the reason your reading this blog to begin with.

How to get an excel spread sheet into SAP.
Well it's really a simple procedure.

First you must create an internal table to hold the spread sheet.

data iexcel type table of alsmex_tabline with header line.

Now then it's a simple call to function module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.
Here is the exact call.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    exporting
      filename                = p_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 8
      i_end_row               = 9000
    tables
      intern                  = iexcel
    exceptions
      inconsistent_parameters = 1
      upload_ole              = 2
      others                  = 3.

The P_Fname is the actual excel Spread Sheet on your PC.
The following code is how the data gets read by ABAP.

loop at iexcel.
  if iexcel-col = '0001'.
    artab-bukrs = iexcel-value.
  endif.
  at end of row.
    append artab.
    clear artab.
  endat.   
  clear iexcel.
endloop.

In the above example I only have one column defined and it has the Company code you would of course define as many columns as you need.
The above code snippet sets up a loop to read thru all records in the internal table. It then checks to see what column the read returned. Then at the end of column check. Add logic to check to see if the row is complete or not. If it is then in this example we will write out a internal table then get the next record.

Well that's it I think I am going to go back to complaining about the snow now.

Monitoring SAP Jobs

On the ISeries/AS400 there is this great product called ROBOT it does job scheduling and it also sends out pages if a job goes into message wait.  I am sure that there are similar products in SAP but the company I work for does not have a tool that will send out messages when a job blows up so I wrote a quick and dirty ABAP program to that.

SAP has a table called TBTCO that contains the status of all jobs that have been run so its a simple process to query that file.
The following is a simple flow of how this process is supposed to work.

As you can see I am using the generic email process that I added to my blog two weeks ago.

You of course can use what ever process you want to send out e-mails.

I did not want to send out e-mails for all job failures so I created a table called ZJOBS_DESC that contains only the job names I am monitoring.

Here is the layout for that file.

I also wanted to be able to send e-mails to multiple people so I created another custom table called ZJOBS_LIST.

Here is the layout of that table.

Well I guess were ready for some ABAP.

Global Variables:

The tables ZEMAIL_* are all part of the generic e-mail process.

tables: zemail_001,
        zemail_002,
        zemail_003a.

data: global_tab_tbtco  type table of tbtco with header line,
      global_tab_desc   type table of zjobs_desc with header line,
      global_tab_list   type table of zjobs_list with header line,
      local_source(100) type c,
      local_date(8)     type c,
      local_time        type sy-timlo,
      local_count       type i,
      local_retain      type sy-datum,
      current_date      type sy-datum,
      current_time      type sy-uzeit.

Following code goes back an hour to get the starting point for querying TBTCO

current_time = sy-uzeit - 3600.
current_date = sy-datum.

if current_time < 0.
  current_time = 230000.
  current_date = sy-datum - 1.
endif.

Query TBTCO

select * from tbtco into table global_tab_tbtco
              where status = 'A'
                and enddate = sy-datum
                and endtime >= current_time.

Now we loop thru the results and check to see if any aborted jobs are in the table ZJOBS_DESC if they are we send out an e-mail.

loop at global_tab_tbtco.
  local_date  = sy-datum.
  local_count = 0.
  get time field local_time.

  select single * from zjobs_desc into global_tab_desc where jobname = global_tab_tbtco-jobname.

  if sy-subrc = 0.
    select single * from zjobs_list into global_tab_list where jobname = global_tab_tbtco-jobname.
    if sy-subrc = 0.
      concatenate global_tab_desc-jobname local_date local_time into zemail_002-source_program.
      zemail_002-com_type        = 'MAL'.
      local_count                = local_count + 1.
      zemail_002-sequence        = local_count.
      zemail_002-email_date      = local_date.
      zemail_002-email_time      = local_time.
      zemail_002-email_address   = global_tab_list-phonenum.
      insert zemail_002.

      zemail_001-status = 'U'.
      local_retain = sy-datum + 180.
      concatenate global_tab_desc-jobname local_date local_time into zemail_001-source_program.
      zemail_001-email_date      = local_date.
      zemail_001-email_time      = local_time.
      zemail_001-kunnr           = global_tab_desc-jobname.
      zemail_001-description     = global_tab_desc-description.
      zemail_001-subject         = global_tab_desc-description.
      zemail_001-retain_until    = local_retain.
      zemail_001-status          = 'U'.
      zemail_001-attachment      = '/usr/sap/transfer/sapout/email/redlight.PDF'.
      zemail_001-name_attach     = 'redlight'.
      zemail_001-type_attachment = 'JPG'.
      zemail_001-created_user    = sy-uname.
      insert zemail_001.

      concatenate global_tab_desc-jobname local_date local_time into zemail_003a-source_program.
      zemail_003a-email_date      = local_date.
      zemail_003a-email_time      = local_time.
      zemail_003a-sequence        = 1.
      zemail_003a-message         = global_tab_desc-description.
      insert zemail_003a.
    endif.
  endif.
endloop.

Again if you don't want to use the generic email process I created then you will want to remove all of the entries to ZEMAIL_* and use your own process but that is it in a nutshell I hope you find it of use. If you would like the source with out all of my comments send me an email.
Here is a couple of books on ABAP that are worth a look at if your new to ABAP.