Bangers uses Presto to transform their green screens for increased efficiency

Hi Ralph,

Many users find green screen interfaces cumbersome to use. Learn how Bangers, a sporting goods distributor, used Presto to modernize their green screen warehousing application in only two weeks.

Faced with the challenge of making one of their most essential RPG applications more intuitive for millennial users, Bangers IT director Todd Glassford knew he needed a solution that could leverage existing, proven RPG code to deliver an updated application.

Using Presto, Bangers’ own PRG programmers were able to deliver an updated application that now has:

  • A modern UI accepted by all users
  • Mobile-accessibility
  • Easier point and click navigation
  • Visual display of information with charts and graphs.

Bangers are very happy with the performance of the modernized application.

“Our millennial staff now have the web UI they wanted,” Says Todd.

Read Bangers’ success story here.

Best Regards,

  Marcel Sarrasin Marcel Sarrasin
VP of Corporate Marketing and Business Development
Fresche
BCD | Quadrant Software | Looksoftware
(250) 655-1882 ext. 114

SNADS over IP

by  | Jun 17, 2012 | Configuration Documents and Tips | 0 comments

SNADS for AS/400 iSeries shops is an invaluable tool. A few years ago I found this document written by Pam Phillips and I would like to thank her for her great work in producing it.

For those of you not familiar with SNADS, it offers a wide variety of helpful features. From sending files, executing remote jobs etc. and it also facilitates more easily moving objects from one machine to another regardless of the OS release.

I hope you find this as helpful as I did. SNADSoverIP

Configuring a RMTOUTQ to Send SPLFs from one IBM System i to Another using LPR/LPD

 

 Technote

This document contains information on configuring and using a Remote Output Queue (RMTOUTQ) to send spooled files to one IBM System i to another using the LPR/LPD (Line Printer Requester/Line Printer Daemon) protocol. Requirements include a TCP/IP interface and IP address configured on both systems and a Line Print Daemon (LPD) server running on the destination system. For information on configuring a Remote Output Queue (RMTOUTQ) for a printer or another type of system (for example, a Microsoft Windows NT Server or UNIX Server), refer to one of the following documents:

8983237, Configuring a Remote Output Queue (RMTOUTQ) Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document ' Configuring a Remote Output Queue (RMTOUTQ)'
24291009, Configuring a Remote Output Queue (RMTOUTQ) to a Thermal Label Printer Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Configuring a Remote Output Queue (RMTOUTQ) to a Thermal Label Printer'

For information on configuring and using a Remote Output Queue (RMTOUTQ) to send spooled files to one IBM System i to another using SNA, refer to the following document:

11855333, Printer Passthrough or Remote Output Queues Database 'AS400 Support Line KnowledgeBase', View 'All Documents', Document 'Printer Passthru or Remote Output Queues '

This document was last updated on 4 January 2011.


Hide details for Requirements and RestrictionsRequirements and Restrictions 
Requirements and Restrictions 

Requirements include a TCP/IP interface configured in the operating system, an IP address on the printer side, and a Line Print Daemon (LPD) TCP/IP process running on the print server. Restrictions include no page range printing. Controlling the output can also be a problem because of the lack of control over the sending of spooled files. Once a spooled file has started sending, a user may have a tough time stopping the printing from the operating system side. Putting the spooled file on hold or deleting it may not be successful. For a more complete listing of restrictions, refer to the following Rochester Support Center knowledgebase document:

14143423, Capabilities and Limitations of Remote Output Queues (RMTOUTQs): Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Capabilities and Limitations of TCP/IP Remote Output Queues'

Hide details for Required PTF LevelsRequired PTF Levels 
Required PTF Levels 

There are no required PTFs in order to print using a Remote Output Queue (RMTOUTQ), but getting the latest Print Group PTF will typically help you avoid problems:

PTF Number Licensed Program Version Description
SF99356 5761SS1 V6R1M0 610 Print Group PTF for V6R1M0
SF99347 5722SS1 V5R4M0 540 Print Group PTF for V5R4M0
SF99346 5722SS1 V5R3M0 530 Print Group PTF for V5R3M0
SF99345 5722SS1 V5R2M0 520 Print Group PTF for V5R2M0

Otherwise for a list of the latest PTFs, including PTFs that are not yet included in the latest Print Group PTF, contact the Rochester Support Center or refer to the Recommended Fixes data base at the following Web site:

http://www-912.ibm.com/s_dir/slkbase.nsf/recommendedfixes 

Hide details for Configuration InstructionsConfiguration Instructions 
Configuration Instructions 

To create a remote output queue, use the Create Output Queue (CRTOUTQ ) command. Specify the following parameters:

Output Queue and Library (OUTQ)
The output queue name can be any name that you choose. It is recommended that the output queue library be set to QUSRSYS in case you need to use a dummy device description for some of your applications. For instructions on creating a dummy device description, refer to the following Rochester Support Center knowledgebase document:

7995114, Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ): Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ)'

Remote System (RMTSYS)
When configuring a RMTOUTQ to another iSeries or AS/400 system, you can either specify the other system’s TCP/IP address, host name or DNS name. If a TCP/IP address is used, the Remote System (RMTSYS) parameter should be set to *INTNETADR and then the other system’s TCP/IP address should be specified in the Internet Address (INTNETADR) parameter which comes later on in the output queue description. If a host name or DNS name is used, that name should be entered in the Remote System (RMTSYS) parameter.

If a TCP/IP address is used, verify that the system can reach the other system by using the PING or Verify TCP/IP Connection (VFYTCPCNN ) command with that TCP/IP address. If a host name or DNS name is used, verify that the system can reach the other system by using the PING or Verify TCP/IP Connection (VFYTCPCNN ) command with that host name or DNS name.

Remote Printer Queue (RMTPRTQ)
This specifies the destination output queue on the other System i system. It is recommended that the fully-qualified output queue name be used and that the output queue be specified in all capital letters. For example, if the RMTOUTQ is to point to PRT01 printer device description on the destination system, specify the device corresponding output queue of the description, ‘QUSRSYS/PRT01’.

Writers to Autostart (AUTOSTRWTR)
Specifies the number of remote writers that are started automatically by the system. It is recommended that this be set to 1, so the remote writer is automatically be started after an IPL, after restarting the QSYS subsystem, or after making a change to the output queue description. If not, the writer must be started manually using the Start Remote Writer (STRRMTWTR ) command.

Connection Type (CNNTYPE)
This must be set to *IP. This indicates that the RMTOUTQ communicates using TCP/IP.

Destination Type (DESTTYPE)
In this case, this must be set to *OS400. This should be set to *OTHERwhen printing spooled files to printers that are attached to the LAN/WAN using a print server, a PC, or a UNIX server, and should be set to *OS400when sending spooled files from one operating system system to another.

Host Print Transform (TRANSFORM)
This is typically set to *NO so the source system will send the operating system spooled file to the other System i system without converting it to an ASCII printer data stream. The spooled file on the destination system will be an exact copy of the spooled file on the source system and, therefore, can usually be routed to any type of printer, including SCS, IPDS, or ASCII printers. This is, of course, dependent on the Printer device type (DEVTYPE) spooled file attribute.

User Data Transform and Library (USRDTATFM)
This is typically set to *NONE when using a RMTOUTQ to send spooled files to another System i system.

Manufacturer Type and Model (MFRTYPMDL)
This is typically set to *NONE when using a RMTOUTQ to send spooled files to another System i system.

Workstation Customizing Object (WSCST)
This is typically set to *NONE when using a RMTOUTQ to send spooled files to another System i system.

Internet Address (INTNETADR)
The Internet Address (INTNETADR) parameter must be set to the IP address for the remote system (OS/400) when the Remote System (RMTSYS) parameter has been set to *INTNETADR. The IP address should be entered without leading zeros (for example, use 192.186.10.2 rather than 192.186.010.002).

Destination options (DESTOPT)
This is typically set to *NONE when using a RMTOUTQ to send spooled files to another System i system.

Print separator page (SEPPAGE)
This is typically set to *NO when using a RMTOUTQ to send spooled files to another System i system.

Hide details for Configuration ExampleConfiguration Example 
Configuration Example 

The following is an example of a Remote Output Queue (RMTOUTQ) to another iSeries or AS/400 system. The “>” indicates important parameters to set. The Remote Output Queue (RMTOUTQ) can be configured using the Create Output Queue (CRTOUTQ ) command. Type CRTOUTQ on an operating system command line, press the F4 (Prompt) Key, fill in the following parameters, and press the Enter Key to create the RMTOUTQ:



Output queue . . . . . . . . . . > SYSTEMB       Name
Library  . . . . . . . . . . . >   QUSRSYS     Name, *CURLIB
Maximum spooled file size:
Number of pages  . . . . . . .   *NONE         Number, *NONE
Starting time  . . . . . . . .                 Time
Ending time  . . . . . . . . .                 Time
+ for more values
Order of files on queue  . . . .   *FIFO         *FIFO, *JOBNBR
Remote system  . . . . . . . . . > *INTNETADR
Remote printer queue . . . . . . > 
Destination OUTQ Name 
Writers to autostart . . . . . .   1             1-10, *NONE
Queue for writer messages  . . .   QSYSOPR       Name
Library  . . . . . . . . . . .     *LIBL       Name, *LIBL, *
Connection type  . . . . . . . . > *IP           *SNA, *IP
Destination type . . . . . . . . > *OS400        *OS400, *OS400
Host print transform . . . . . .  > *NO           *YES, *NO
Manufacturer type and model  . . > *NONE

Workstation customizing object   > *NONE         Name, *NONE         
  Library  . . . . . . . . . . .                 Name, *LIBL, *CURLIB
Internet address . . . . . . . . > 
Destination IP Address 
Destination options  . . . . . . > *NONE
Text 'description' . . . . . . . > 'RMTOUTQ to another iSeries or AS/400'

Hide details for Configuring a Dummy Device Description to Use with a Remote Output QueueConfiguring a Dummy Device Description to Use with a Remote Output Queue 
Configuring a Dummy Device Description to Use with a Remote Output Queue 

Dummy device descriptions are typically used when printing from applications that do not support printing directly to an output queue but only to a printer device description (including Query/400 and the System/36 Environment). They are also useful when moving spooled files from one printer to another from the Work with Output Queue (WRKOUTQ ) or Work with Spooled Files (WRKSPLF ) commands or when using Operations Navigator to drag and drop spooled files from one printer to another.

This information can also be found in the following Rochester Support Center knowledgebase document:

7995114, Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ) Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ)'

Use the following steps to create a dummy device description:

1. Before a dummy device description can be created, the remote output queue (RMTOUTQ) must reside in the QUSRSYS library. If not, creating the dummy device description will cause you to have two output queues of the same name — one in the RMTOUTQ’s current library and one in QUSRSYS. Use the following Work with Objects (WRKOBJ ) command to verify that the RMTOUTQ is in library QUSRSYS:WRKOBJ OBJ(*ALL/output-queue) OBJTYPE(*OUTQ)
2. To move the RMTOUTQ to library QUSRSYS, first use the End Writer (ENDWTR ) command with the When to end writer (OPTION) parameter set to *IMMED to end the writer immediately:ENDWTR WTR(output-queue) OPTION(*IMMED) 

Then issue the following Move Object (MOVOBJ ) command:

MOVOBJ OBJ(outq-library/output-queue) OBJTYPE(*OUTQ) TOLIB(QUSRSYS)

3. Use the following Create Device Desc (Printer) (CRTDEVPRT ) command to create a device description with the same name as the RMTOUTQ:CRTDEVPRT DEVD(output-queue-name) DEVCLS(*VRT) TYPE(3812) MODEL(1) ONLINE(*NO) FONT(011)
4. Creating the dummy device description should result in MSGCPD2602,Device name &1 already exists . This indicates that the printer device description is associated with the Remote Output Queue (RMTOUTQ) that has already been created.If this message is not received, it most likely means that the RMTOUTQ was not in library QUSRSYS and the Create Device Desc (Printer) (CRTDEVPRT ) command has created a second output queue (but not a RMTOUTQ) with the same name in library QUSRSYS. The problem with this is that applications will typically send spooled files to the output queue in library QUSRSYS, but since this is not a RMTOUTQ, the spooled files will not be sent to the printer.
5. If working in the System/36 Environment, use the Display System/36 Configuration (DSPS36 ) command to check the System/36 printer IDs. The Change System/36 Configuration (CHGS36 ) command can be used to change the System/36 printer IDs or to assign a new System/36 printer ID to point to the new dummy device description.Note: When you specify a printer in the //PRINTER OCL statement, you are using the System/36 printer ID for that printer. The System/36 Environment then uses a print file in library #LIBRARY that has the same name as the printer ID. If the output is not going to the correct printer, either specify the output queue name on the //PRINTER OCL status or use the Change Printer File (CHGPRTF ) command to point the printer file directly to the remote output queue:

CHGPRTF FILE(#LIBRARY/system-36-printer-id) OUTQ(outq-library/output-queue)

Notes:

o Do not use the Vary Configuration (VRYCFG ) or the Work with Configuration Status (WRKCFGSTS ) commands to vary on the printer device description.
o Do not use the Start Print Writer (STRPRTWTR ) command or Option 1 (Start) from the Work with Writers (WRKWTR ) command to start the writer. Use the Start Remote Writer (STRRMTWTR ) command to start a writer using the Remote Output Queue (RMTOUTQ).
o Remote Output Queues (RMTOUTQs) use remote writers, not printer writers. However, the WRKWTR command defaults to WTR(*PRT) which shows only the active and inactive printer writers. Therefore, using theWRKWTR command without any parameters shows the writer to be in END status, even when the remote writer is started.
o To see if the remote writer is started, use the Work with Writers (WRKWTR ) command with the Writer (WTR) parameter set to *ALL.For example:

WRKWTR WTR(*ALL) 

This will show all active writers instead of just showing all of the active and inactive printer writers.

Hide details for Changing a Remote Output Queue (RMTOUTQ) DescriptionChanging a Remote Output Queue (RMTOUTQ) Description 
Changing a Remote Output Queue (RMTOUTQ) Description 

Once a Remote Output Queue (RMTOUTQ) has been configured, there is no need to delete and re-create the RMTOUTQ to make any changes. The RMTOUTQ description can be changed by doing the following:

1. Use the End Writer (ENDWTR ) command with the When to end writer (OPTION) parameter set to *IMMED to end the writer immediately.
2. Use the Work with Output Queue Description (WRKOUTQD ) command to display the current settings for the RMTOUTQ.
3. Verify that the status shows that a writer is not active to this output queue.
4. Press the F13 (Change) Key to run the Change Output Queue (CHGOUTQ ) command.
5. Make the desired changes to the RMTOUTQ description, and press the Enter Key.
6. If needed, use the Start Remote Writer (STRRMTWTR ) command to restart the writer.

Hide details for ReferencesReferences 
References 

Refer to the following Rochester Support Center knowledgebase documents:

8983237, Configuring a Remote Output Queue (RMTOUTQ): Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document ' Configuring a Remote Output Queue (RMTOUTQ)'
24291009, Configuring a Remote Output Queue (RMTOUTQ) to a Thermal Label Printer Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Configuring a Remote Output Queue (RMTOUTQ) to a Thermal Label Printer'
8233537, Recommended Remote Printer Queue Values for Remote Output Queues (RMTOUTQs) Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Recommended Remote Printer Queue Values for Remote Output Queues (RMTOUTQs)'
7995114, Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ) Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ)'
14143423, Capabilities and Limitations of Remote Output Queues (RMTOUTQs): Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Capabilities and Limitations of TCP/IP Remote Output Queues'
16483900, Forcing a Remote Output Queue (RMTOUTQ) to Select a Particular Paper Size Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software\Knowledge Base Documents', Document ' Forcing a Remote Output Queue (RMTOUTQ) to Select a Particular Paper Size'
17690939, Information on Printers from Various Manufacturers Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software\Knowledge Base Documents', Document 'Support for Various ASCII Printers'
11944305, Version 5 Printer Model Settings for Host Print Transform (HPT) Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Version 5 Printer Model Settings for Host Print Transform (HPT)'
11401901, Using Host Print Transform (HPT) with a Remote Output Queue (RMTOUTQ) Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'Using Host Print Transform (HPT) with a Remote Output Queue (RMTOUTQ)'
23383453, V5Rx PTF Listing for TCP/IP and LAN Printing Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software\Knowledge Base Documents', Document 'V5Rx PTF Listing for TCP/IP and LAN Printing'
12594243, TSPRWPR Remote Writer Page Range Support Exit Program Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software', Document 'TSPRWPR Remote Writer Page Range Support Exit Program'
18762910, Configuring a *LAN 3812 Device Description that Uses the LPR Print Driver (TSPLPRD) Exit Program Database 'Rochester Support Line KnowledgeBase', View 'All Documents\All Software\Knowledge Base Documents', Document 'Configuring a *LAN 3812 Device Description that Uses the LPR Print Driver (TSPLPRD) Exit Program'

System i Support 

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright

    •  ©

1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012

IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.

Moving data from one iSeries server to another

A number of situations occur in enterprise operations that could require moving data from one iSeries server to another. For example, a new dealership might open in a region, and some clients from one or two other dealerships might be transferred to the new dealership as determined by client address. Perhaps a dealership closed or no longer represents Spiffy Corporation sales and service. That dealer’s inventories and required service information must be allocated to either the regional office or other area dealerships. Perhaps a dealership has grown to the extent that it needs to upgrade its server, and the entire database must be moved to the new server.

Some alternatives for moving data from one iSeries server to another are:

  • User-written application programs
  • Interactive SQL (ISQL)
  • DB2® UDB for iSeries Query Management functions
  • Copy to and from tape or diskette devices
  • Copy file commands with DDM
  • The network file commands
  • iSeries server save and restore commands

Creating a User-Written Application Program

A program compiled with DUW connection management can connect to a remote database and a local database and FETCH from one to INSERT into the other to move the data. By using multi-row FETCH and multi-row INSERT, blocks of records can be processed at one time. Commitment control can be used to allow checkpoints to be performed at points during the movement of the data to avoid having to start the copy over in case of a failure.

Querying a database using Interactive SQL

Using the SQL SELECT statement and interactive SQL, you can query a database on another iSeries server for data you need to create or update a table on the local server. The SELECT statement allows you to specify the table name and columns containing the desired data, and selection criteria or filters that determine which rows of data are retrieved. If the SELECT statement is successful, the result is one or more rows of the specified table.

In addition to getting data from one table, SQL allows you to get information from columns contained in two or more tables in the same database by using a join operation. If the SELECT statement is successful, the result is one or more rows of the specified tables. The data values in the columns of the rows returned represent a composite of the data values contained in specified tables.

Using an interactive SQL query, the results of a query can be placed in a database file on the local server. If a commitment control level is specified for the interactive SQL process, it applies to the application server (AS); the database file on the local server is under a commitment control level of *NONE.

Interactive SQL allows you to do the following:

  • Create a new file for the results of a select.
  • Replace and existing file.
  • Create a new member in a file.
  • Replace a member.
  • Append the results to an existing member.

Consider the situation in which the KC105 dealership is transferring its entire stock of part number ‘1234567’ to KC110. KC110 queries the KC105 database for the part they acquire from KC105. The result of this inventory query is returned to a database file that already exists on the KC110 server. This is the process you can use to complete this task:

Use the Start SQL (STRSQL) command to get the interactive SQL display. Before you enter any SQL statement (other than a CONNECT) for the new database, specify that the results of this operation are sent to a database file on the local server by doing the following steps:

  1. Select the Services option from the Enter SQL Statements display.
  2. Select the Change Session Attributes option from the Services display.
  3. Enter the Select Output Device option from the Session Attributes Display.
  4. Type a 3 for a database file in the Output device field and press Enter. The following display is shown:
    Change File
    
    Type choices, press Enter.
    
    File . . . . . . . . .   QSQLSELECT   Name
    Library  . . . . . .   QGPL         Name
    Member . . . . . . . .   *FILE        Name, *FILE, *FIRST
    
    Option . . . . . . . .   1            1=Create new file
    2=Replace file
    3=Create new member
    4=Replace member
    5=Add to member
    
    For a new file:
    Authority  . . . . .   *LIBCRTAUT   *LIBCRTAUT, *CHANGE, *ALL
    *EXCLUDE, *USE
    authorization list name
    
    Text . . . . . . . .
    
    F3=Exit     F5=Refresh     F12=Cancel
  5. Specify the name of the database file that is to receive the results.

When the database name is specified, you can begin your interactive SQL processing as shown in the example below.

Enter SQL Statements

Type SQL statement, press Enter.
Current connection is to relational database KC000.
CONNECT TO KC105__________________________________________________________
Current connection is to relational database KC105.
====> SELECT * FROM INVENTORY_____________________________________________
WHERE PART = '1234567'____________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
Bottom
F3=Exit   F4=Prompt   F6=Insert line   F9=Retrieve   F10=Copy line
F12=Cancel            F13=Services     F24=More keys

For more information on the SQL programming language and interactive SQL, see the SQL Programming Concepts and the SQL Reference topics in the iSeries Information Center.

Querying remote servers using DB2 UDB for iSeries Query Management function

The DB2 UDB for iSeries Query Management function provides almost the same support as interactive SQL for querying a remote server and returning the results in an output file to the local server.

Both interactive SQL and the query management function can perform data manipulation operations (INSERT, DELETE, SELECT, and so on) for files or tables without the requirement that the table (or file) already exist in a collection (it can exist in a library). Also, query management uses SQL CREATE TABLE statements to provide data definition when a new table is created on the server as a result of the query. Tables created from a query management function follow the same guidelines and restrictions that apply to a table created using SQL.

However, the query management function does not allow you to specify a member when you want to add the results to a file or table. The results of a query function are placed in the first file member unless you use the Override with Database File (OVRDBF) command to specify a different member before starting the query management function.

For more information on the query management function, see the Query Management Programming book.

Copying files to and from tape or diskette

You can copy a table or file to tape or diskette using the Copy to Tape (CPYTOTAP) andCopy to Diskette (CPYTODKT) commands on the iSeries server.

Data on tape or diskette can be loaded on another server using the Copy from Tape (CPYFRMTAP) and Copy from Diskette (CPYFRMDKT) commands. For more information about using these commands, see the Storage solutions topic.

You can also use the Copy File (CPYF) command to load data on tape into DB2 UDB for iSeries. This is especially useful when loading data that was unloaded from DB2 UDB for z/OS®, or DB2 UDB Server for VM (SQL/DS™). Nullable data can be unloaded from these servers in such a way that a single-byte flag can be associated with each nullable field. CPYF with the *NULLFLAGS option specified for the FMTOPT parameter can recognize the null flags and ignore the data in the adjacent field on the tape and make the field null in DB2 UDB for iSeries. Another useful FMTOPT parameter value for importing data from IBM® mainframes is the *CVTFLOAT value. It allows floating point data stored on tape in System/390® format to be converted to the IEEE format used by DB2 UDB for iSeries.

Moving data between iSeries servers using Copy File Commands

Another way to move data from one iSeries server to another is to copy the data using the copy file commands with DDM. You can use the Copy File (CPYF)Copy Source File (CPYSRCF), and Copy From Query File (CPYFRMQRYF) commands to copy data between files on source and application source (AS)s. You can copy local relational database or device files from (or to) remote database files, and remote files can also be copied to remote files.

For example, if a dealership closes, the distributed relational database administrator can copy the client and inventory tables from the remote server to the local regional server. The administrator needs a properly authorized user profile on the application source (AS) to access and copy the tables and must create a DDM file on the application requester (AR) for each table or file that is copied. The following example shows the command the database administrator would use to copy a table called INVENT in a collection called SPIFFY from a server with a remote location name of KC105 to a regional center server called KC000. A DDM file called INCOPY in a library called TEST on the application requester (AR) KC000 is used for the file access. These commands are run on the KC000 server:

CRTDDMF   FILE(TEST/INCOPY) RMTFILE(SPIFFY/INVENT)
   RMTLOCNAME(KC105)
CPYF      FROMFILE(TEST/INCOPY)  TOFILE(TEST/INVENTDDM)
   MBROPT(*ADD)

In this example, the administrator runs the commands on the KC000 server. If the administrator is not on the KC000 server, then pass-through must be used to run these commands on the KC000 server. The Submit Remote Command (SBMRMTCMD) command cannot be used to run the above commands because the iSeries server cannot be a application requester (AR) and a application source (AS) for the same job.

Consider the following items when using this command with DDM:

 

  • When a delete-capable file is copied to a non-delete capable file, you must specify COMPRESS(*YES), or an error message is sent and the job ends.

 

  • If the remote file name on a DDM file specifies a member name, the member name specified for that file on the Copy File (CPYF) command must be the same as the member name on the remote file name on the DDM file. In addition, the Override with Database File (OVRDBF) command cannot specify a member name that is different from the member name on the remote file name on the DDM file.

 

 

  • If the TOFILE parameter is a DDM file that refers to a file that does not exist, CPYF creates the file. Following are special considerations for remote files created with theCopy File (CPYF) command:
    • The user profile for the target DDM job must be authorized to the Create Physical File (CRTPF) command on the application source (AS).
    • For an iSeries target, the TOFILE parameter has all the attributes of the FROMFILE parameter except those described in the File Management topic in the iSeries Information Center.

 

For more information about using the Copy File commands to copy between servers, see theDistributed Data Management topic in the iSeries Information Center.

Transferring data over networks using Network File Commands

Data can be transferred over networks protocols that support SNA distribution services (SNADS). In addition to APPC and APPN protocols used with distributed relational database processing, SNADS can be used with binary synchronous equivalence link (BSCEL) and SNA Upline Facility (SNUF) protocols. An iSeries server supported by SNADS can send data to another server with the Send Network File (SNDNETF) command and receive a network file from another server with the Receive Network File (RCVNETF) and Work with Network Files (WRKNETF) commands.

Moving a table using server save and restore commands

You can move a table from another iSeries server using the Save Object (SAVOBJ) andRestore Object (RSTOBJ) commands. The save commands save database files on tape, diskette, or a save file. The save file can be distributed to another server through communications.

The save and restore commands used to save and restore tables or files include:

For example, if two dealerships were merging, the save and restore commands could be used to save collections and tables for one relational database, which are then restored on the remaining server’s relational database. To accomplish this an administrator would:

  1. Use the Save Library (SAVLIB) command on server A to save a collection or use theSave Object (SAVOBJ) command on server A to save a table.
  2. Specify whether the data is saved to a save file, which can be distributed using SNADS, or saved on tape or diskette.
  3. Distribute the save file to server B or send the tape or diskette to server B.
  4. Use the Restore Library (RSTLIB) command on server B to restore a collection or use the Restore Object (RSTOBJ) command on server B to restore a table.

A consideration when using the save and restore commands is the ownership and authorizations to the restored object. A valid user profile for the current object owner should exist on the server where the object is restored. If the current owner’s profile does not exist on this server, the object is restored under the QDFTOWN default user profile. User authorizations to the object are limited by the default user profile parameters. A user with QSECOFR authority must either create the original owner’s profile on this server and make changes to the restored object ownership, or specify new authorizations to this object for both local and remote users.

For more information about the save and restore commands, see the Backup and Recoverytopic in the iSeries Information Center.

How to remove leading spaces in cells in Excel?

Sometimes there are some blank spaces before strings in cells, these spaces are called leading spaces. It must be arduous to remove leading spaces in cells one by one. Actually, there are several smart tricks that can help us remove leading spaces in cells conveniently and quickly.

Remove leading spaces in cells with Trim function

Remove leading spaces in cells with VBA

Remove leading spaces in cells with Kutools for Excel

Suppose we paste some content from Microsoft Word with indent spaces, then it shows the leading spaces in each cell as the following screenshot shows.

Recommended Productivity Software

Office TabUse tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for OfficeBrings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).


arrow blue right bubble Remove leading spaces in cells with Trim function

The Trim function in Microsoft Excel can remove all spaces from text except for single spaces between words.

Step 1: Enter the formula =TRIM (A1) in the adjacent cell C1 and press the Enter key.

Step 2: Select cell C1 and drag the fill handle down to the range cell that you want to remove the leading space. Then you can see all cell content are extracted with all leading spaces removed. See screenshot:

You can replace the original content with the extracted ones by copying and pasting them as values.

This method is quite easy if you only need to remove leading spaces in contiguous cells within a row or a column. However, when you need to remove leading spaces in a range with several rows and columns, you have to use the function several times.


arrow blue right bubble Remove leading spaces in cells with VBA

If you are experienced with Microsoft Excel,  VBA macro is a better choice to remove the leading spaces in cells.

Step 1: Hold down the Alt + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

Step 2: Click Insert > Module, and paste the following macro in the Module window.

VBA: Remove leading spaces:

1
2
3
4
5
6
7
8
9
10
11
12
Sub RemoveLeadingSpace()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

Step 3: Press the F5 key to run this macro. In the pop-up KutoolsforExcel dialog box, you need to select a range with leading space you want to remove, and then click the OK button. See screenshot:

Then you can see all leading spaces are moved from the selected range, see screenshots:


arrow blue right bubble Remove leading spaces in cells with Kutools for Excel

If you have Kutools for Excel installed, you can remove all leading spaces of cells in a selection conveniently and quickly.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Select the range which you want to remove all leading spaces in cells.

Step 2: Click the Kutools > Text Remove Spaces. See screenshot:

Step 3: In the Remove Spaces dialog box, check the Leading Spaces option in Spaces Type section, and then click the OK or Apply button. See screenshot:

Now all leading spaces are removed from the selected cells.

Kutools for Excel’s Remove Spaces tool makes it possible to delete all kinds of spaces from strings in cells, including all extra spaces, leading spaces, trailing spaces, or all spaces. Click to know more…


Related articles:


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office TabUsing handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for OfficeBring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don’t need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools…

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase

how to split text cells into multiple columns in excel

If you copy data from another program and paste it into Microsoft Excel, Excel may condense several columns of data to a single column. You can use the Text To Columns command on the Data menu to put each record in a separate column. The range that you convert may contain many rows, but you can only convert one column of data at a time.

To create an example that demonstrates how to use the Text To Columns command:

  1. Create a new workbook.
  2. In Sheet1, type the following data:
          A1: The,dog,ran
          A2: abc,def
          A3: abcdefg,hijkl
          A4: 2554,4874,41
  3. Select the range A1:A4.
  4. On the Data menu, click Text to Columns.
  5. In Step 1 of the Convert Text To Columns Wizard, make sure that the Delimited option is selected, and then clickNext.
  6. In Step 2 of the Convert Text To Columns Wizard, click to select the Comma check box under Delimiters, and then click Next.NOTE: All of the check boxes under Delimiters should be cleared except for the Comma check box.
  7. In Step 3 of the Convert Text To Columns Wizard, type B1 in the Destination box, and then click Finish.

Your data is parsed into separate columns, beginning with column B, and the data in Sheet1 should resemble the following:

   A1: The,dog,ran     B1: The     C1: dog   D1: ran
   A2: abc,def         B2: abc     C2: def
   A3: abcdefg,hijkl   B3: abcdefg C3: hijkll
   A4: 2554,4874,41    B4: 2554    C4: 4874  D5: 41

Note The example illustrates data with commas as the delimiter. This is only one of several delimiters that you may see in your data. For example, if you have Names in a column of data, consisting of first and last name in a single cell separated by a Space, the Space character can be used as a delimiter to separate the first and last names into adjacent cells instead of the same cell.

How to delete duplicate records from a table in Access 2000

Article ID: 209183 – View products that this article applies to.
This article was previously published under Q209183
For a Microsoft Access 97 version of this article, see 109329.

Novice: Requires knowledge of the user interface on single-user computers.

Expand all | Collapse all

SUMMARY

This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.

MORE INFORMATION

To remove duplicate records from a table, follow these steps:

  1. Make a copy of the structure of the table from which you want to remove the duplicate records.To copy a table:
    1. Select the table in the Database window
    2. On the Edit menu, click Copy.
    3. On the Edit menu, click Paste.
    4. Enter a name for the new table.
    5. Select Structure Only
    6. Click OK.
  2. Open the new table in Design view.
  3. Select the field(s) that contain the duplicate values.
  4. To make your selection the primary key for the table, click the Primary Key button on the toolbar.
  5. Save and close the table.
  6. Create an append query based on the original table containing duplicates.
  7. In the query Design View, on the Query menu, click Append Query.
  8. In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
  9. Include all the fields from the original table by dragging the asterick (*) to the query design grid.
  10. On the Query menu, click Run.
  11. Click Yes in the dialog box advising you that you are about to append records.
  12. Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:
    Microsoft Access can’t append all the records in the append query.Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn’t add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
    Do you want to run the action query anyway?
    To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help.
  13. Click Yes.
  14. View the contents of the new table. When you’re sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.

REFERENCES

For more information about deleting duplicate records, click Microsoft Access Help on the Help menu, type Automatically delete duplicate records from a table. in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209183 – Last Review: July 29, 2004 – Revision: 3.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords:
kbhowto kbdatabase kbusage KB209183

HTML Copyright and trademark symbols

Copyright and trademark symbols
Copyright and trademark symbols HTML code

Copyright and registered trademark symbols can not easily be copy pasted into your html webpage so you should use the Copyright or registered trademark html symbol code instead.

Find the Copyright or registered trademark symbol you want below and then copy/paste the html smiley Copyright or registered trademark code highlighted in red into your html document.

 ™

&#8482;

&#8480;

 ©

&#169;

®

&#174;

 ℗

&#8471;

How to: Remove or replace line breaks and carrage returns in cells

There are few quick methods to remove the line breaks and carriage returns in cells.
By default you might not know an easy and quick way to do this but it is fairly simple, either with just Excel, or with the help of ASAP Utilities.

Let’s say we have the following example.
In this case the author has used the shortcut Alt+Enter to put the last name on a new line.Initial dataset
But now we need it together on a single line, with a space between the first- and last name instead of a line break.
Instead of:
Bastien
Mensink

we need
Bastien Mensink, etc. etc.

Method 1, with Excel only

If you want to replace these line-breaks with for example a space, you can do this with Excel’s find/replace command.

In the “Find what:” box you can enter the line-break code, which is character number 10.
To do this, press and hold the Alt key, and then enter the numbers 010 from the numeric part of the keyboard.
On my computer in the box there is then a small dot blinking when the cursor is still in the entry box.
To enter the numbers you need to use the numbers from the Num-lock part of the keyboard (with the numbers above the letters on your keyboard it doesn’t work). If you have data imported from another source, sometimes the line breaks are also represented by character 13 instead of 10.

In the “Replace with:” dialog you can enter a space to have the line-breaks replaced with spaces. You can also leave that blank to have the line-breaks removed.
Excel's Find and Replace dialogue

Method 2, with ASAP Utilities

You can use the following tool from ASAP Utilities to remove or replace the line-breaks:
Text » Advanced character remove or replace…

In the dialogue you can click on the “Line breaks” button to remove or replace the line break characters in the selected cells:Remove the line breaks/carriage returns with ASAP Utilities

Method 3, with ASAP Utilities

You can use the following tool from ASAP Utilities to remove or replace the line-breaks:
Range » Find and/or replace in all sheets…

To search for the line break you can enter the code {lf}, to search for the carriage return you can use {cr}:ASAP Utilities Find and/or replace in all sheets

Filed under: ASAP UtilitiesHow tohow to with ASAP Utilities on January 18th, 2010 by Bastien | 3 Comments »

3 Responses to “How to: Remove or replace line breaks and carrage returns in cells”

  1. You can also use ctrl-j instead of alt-0010 in excel’s edit|replace dialog.

  2. Hello Dave,

    Thank you for your feedback.
    I didn’t know that, thanks for sharing!

    Kind regards,
    Bastien

  3. Kanwaljit, on February 1st, 2010 at 1:49 pm Said:

    Hi Bastien,

    May the Couple Live 1000 years !

    If we put , (comma) when using the File Types option “Text File, Delimited by :” in Utility 16-Import->10 Merge and Import Multiple Files” the Utility starts a give an error message “Error 1004 : Method ~ of Object ~ Failed”. Other file type options seems to be working fine.

    Regards
    CA Kanwaljit Singh Dhunna

Originally posted on ASAP Utilities