The present disclosure relates to computer systems, and, in particular, to methods, systems, and computer program products for managing information in a computer database.
Computer database management systems (DBMS) may provide utilities for extracting portions of data that are stored in a database for use by other applications or devices. For example, DB2 is a relational database management system (RDBMS) that provides a utility called UNLOAD. The UNLOAD utility copies data from one or more source objects, such as a table, and outputs the copied data into a format that can be specified by the user through the command line or query used execute the operation. It can be difficult, however, for a user to determine the correct syntax and/or options to use in the command line and/or query to extract the data in the format that the user desires. This may result in multiple invocations of the UNLOAD utility as the user attempts to construct a command line and/or query with the correct syntax/options, which can waste processor time and/or memory if, for example, the output file(s) in the undesired format(s) are not promptly deleted.
In some embodiments of the inventive subject matter, a method comprises, performing by a host system processor: detecting a first query of a database, detecting a first relation that is generated responsive to the first query of the database, the first relation comprising a record having a plurality of fields, each of the plurality of fields having a format associated therewith, receiving user input that changes the format associated with one of the plurality of fields from a first format to a second format, generating a second query for the database based on the user input, performing the second query of the database, generating a second relation responsive to the second query of the database, the second relation comprising a plurality of records, each of the plurality of records comprising the plurality of fields, and communicating the second relation to a client device application. One of the plurality of fields in each of the plurality of records has the second format.
In other embodiments of the inventive subject matter, a system comprises a processor and a memory coupled to the processor and comprising computer readable program code embodied in the memory that is executable by the processor to perform: detecting a first query of a database, modifying the first query of the database to generate a modified first query for the database, performing the modified first query of the database, detecting a first relation that is generated responsive to the modified first query of the database, the first relation comprising a record having a plurality of fields, each of the plurality of fields having a format associated therewith, receiving user input that changes the format associated with one of the plurality of fields from a first format to a second format, the second format being used in a client device application, generating a second query for the database based on the user input, performing the second query of the database, generating a second relation responsive to the second query of the database, the second relation comprising a plurality of records, each of the plurality of records comprising the plurality of fields, and communicating the second relation to the client device application. One of the plurality of fields in each of the plurality of records has the second format.
In further embodiments of the inventive subject matter, a computer program product comprises a tangible computer readable storage medium comprising computer readable program code embodied in the medium that is executable by a processor to perform: detecting a first query of a database, detecting a first relation that is generated responsive to the first query of the database, the first relation comprising a record having a plurality of fields, each of the plurality of fields having a format associated therewith, receiving user input that changes the format associated with one of the plurality of fields from a first format to a second format, generating a second query for the database based on the user input, performing the second query of the database, generating a second relation responsive to the second query of the database, the second relation comprising a plurality of records, each of the plurality of records comprising the plurality of fields, and communicating the second relation to a client device application. One of the plurality of fields in each of the plurality of records has the second format and the first relation consumes less memory than the second relation.
It is noted that aspects described with respect to one embodiment may be incorporated in different embodiments although not specifically described relative thereto. That is, all embodiments and/or features of any embodiments can be combined in any way and/or combination. Moreover, other methods, systems, articles of manufacture, and/or computer program products according to embodiments of the inventive subject matter will be or become apparent to one with skill in the art upon review of the following drawings and detailed description. It is intended that all such additional systems, methods, articles of manufacture, and/or computer program products be included within this description, be within the scope of the present inventive subject matter, and be protected by the accompanying claims. It is further intended that all embodiments disclosed herein can be implemented separately or combined in any way and/or combination.
Other features of embodiments will be more readily understood from the following detailed description of specific embodiments thereof when read in conjunction with the accompanying drawings, in which:
In the following detailed description, numerous specific details are set forth to provide a thorough understanding of embodiments of the present disclosure. However, it will be understood by those skilled in the art that the present invention may be practiced without these specific details. In some instances, well-known methods, procedures, components and circuits have not been described in detail so as not to obscure the present disclosure. It is intended that all embodiments disclosed herein can be implemented separately or combined in any way and/or combination. Aspects described with respect to one embodiment may be incorporated in different embodiments although not specifically described relative thereto. That is, all embodiments and/or features of any embodiments can be combined in any way and/or combination.
As used herein, a “service” includes, but is not limited to, a software and/or hardware service, such as cloud services in which software, platforms, and infrastructure are provided remotely through, for example, the Internet. A service may be provided using Software as a Service (SaaS), Platform as a Service (PaaS), and/or Infrastructure as a Service (IaaS) delivery models. In the SaaS model, customers generally access software residing in the cloud using a thin client, such as a browser, for example. In the PaaS model, the customer typically creates and deploys the software in the cloud sometimes using tools, libraries, and routines provided through the cloud service provider. The cloud service provider may provide the network, servers, storage, and other tools used to host the customer's application(s). In the IaaS model, the cloud service provider provides physical and/or virtual machines along with hypervisor(s). The customer installs operating system images along with application software on the physical and/or virtual infrastructure provided by the cloud service provider.
As used herein, the term “data processing facility” includes, but it is not limited to, a hardware element, firmware component, and/or software component. A data processing system may be configured with one or more data processing facilities.
Embodiments of the inventive subject matter are described herein in the context of extracting data from a relational database, such as a DB2 database using the UNLOAD utility. It will be understood that embodiments of the inventive subject matter are not limited in their application to a relational database model as other database models, such as, but not limited to a flat database model, a hierarchical database model, a network database model, an object-relational database model, and a star schema database model may also be used.
Some embodiments of the inventive subject matter stem from a realization that when extracting data from a database on a host system for use by an application, for example, on another device or system, it can be difficult to generate a command line and/or query that includes the proper syntax and/or options to format the extracted data in a manner that is suitable for destination application. A user may have to consult technical manuals to determine how to construct the database command line and/or query, which may be time consuming and potentially difficult to understand if the user is not experienced performing transactions with the database. Because of the difficulty a user may have in constructing a command line and/or query that extracts the data from the database in a desired format, numerous extractions may be attempted with the resulting extracted data having undesired formats. This may waste processor time and/or memory if the undesired data extractions are not deleted in a timely manner In some cases, a user may give up trying to extract the data in a desired format and instead use another tool to edit that data that was extracted to correct any undesirable formatting.
According to some embodiments of the inventive subject matter, a first query for extracting a copy of data from a database, such as an invocation of the UNLOAD utility for a DB2 database, may be detected along with a first relation that is generated in response to the query. This first relation may have one or more records that each have a plurality of fields and each of the fields may have a format associated therewith. An output file editor may provide a visual interface to allow a user to change the formats for one or more of the fields in the records in the first relation to desired formats. These desired formats may be based on the types of formats that are compatible with a client application on another device or system, for example. An output file comparator may determine what format changes a user makes to the first relation and may generate a second query for extracting data from the database based on those format changes. The second query may be a command line and/or a query with the proper syntax and options for extracting the copy of the data from the database with the data values formatted according to the user's preferences. Thus, embodiments of the inventive subject matter may alleviate a user from the need to understand the details of how to construct command lines and/or queries for extracting data from a database where the data values are formatted according to the user's preferences. The second query may be performed on the database and a second relation may be generated having one or more records with the fields in the records having associated formats that encompass the format changes made by the user. The second relation may be communicated to a client device or system for further processing by an application. In some embodiments of the inventive subject matter, the first query may be intercepted and modified so that that the number of records in the first relation is reduced to a number that is sufficient for a user to make the desired field format changes. This may reduce the processor time spent in extracting the data for the first relation along with the amount of memory consumed by the first relation (relative to a relation obtained by the unmodified first query) as many of these records are unnecessary inasmuch as the first relation is used solely for the purposes of verifying and/or changing the formats associated with one or more fields. Once the format changes have been made by the user, the first relation may be automatically deleted so as to reduce the impact on the host system's memory.
Referring to
As shown in
The clients and servers can communicate using a standard communications mode, such as Hypertext Transport Protocol (HTTP), SOAP, XML-RPC, and/or WSDL. According to the HTTP request-response communications model, HTTP requests are sent from the client to the server and HTTP responses are sent from the server to the client in response to an HTTP request. In operation, the server waits for a client to open a connection and to request information, such as a Web page. In response, the server sends a copy of the requested information to the client, closes the connection to the client, and waits for the next connection. It will be understood that the server can respond to requests from more than one client.
Although
Referring now to
As shown in
The monitor module 325 may be configured to detect a first query for extracting a copy of data from a database. For example, for a DB2 database, the monitor module 325 may detect the invocation of the UNLOAD utility. In some embodiments, the monitor module 325 may intercept the query and generated a modified first query to obtain fewer records from the database than the original query was designed to obtain. The modified first query may be designed to extract a number of records that is sufficient for a user review the formats associated with the various data fields and make any desired changes. These format changes may be based, for example, on data formats used in an application that runs on another client system and/or client device. By modifying the first query to reduce the number of records extracted, both processor time and memory usage may be reduced. Specifically, the amount of memory consumed by a relation obtained based on the modified first query may be less than the amount of memory consumed by a relation obtained based on the first query without modification. The monitor module 325 may be further configured to detect when a relation is generated based on the original first query or the modified first query. This relation may be provided as an input to the output file editor module 330.
The output file editor module 330 may be configured to provide a visual interface of the first relation generated from the query or modified query of the database to allow a user to change the formats for one or more of the fields in the records to desired formats. In accordance with some embodiments, the data formats associated with the fields may include, but are not limited to, data formats, integer formats, numerical sign formats, and time formats. In general, a user may change the format associated with a field for any type of format that may be expressed in multiple ways. The format(s) associated with one or more fields may be changed so as to be compatible, for example, with a client application running on another system or device.
The output file comparator module 335 may be configured to determine what format changes a user has made to the first relation using the output file editor module 330. In some embodiments, the output file editor 330 may maintain two different files: a first file corresponding to the original first relation generated by the query or modified query of the database and a second file corresponding to a modified version of the original first relation after a user has made changes to the format(s) of one or more fields. The output file comparator module 335 may compare these two files to determine the format changes made by the user. In other embodiments, the output file comparator module 335 may cooperate with the output file editor 330 to determine the format changes made by the user as the user makes the changes without the need to maintain two different files and perform a file comparison. The output file comparator module 335 may be configured to remove the original first relation generated by the first query or modified first query and, if applicable, the modified version of the original first relation containing the user's format changes once the output file comparator module 335 has determined what format changes have been made to reduce the impact of these files on memory availability.
The query generation module 340 may be configured to generate a second query for extracting data from the database based on the format changes determined by the output file comparator module 335. The second query may have the appropriate syntax, options, and the like to be used in a query or command line so that the relation that is extracted from the database has fields with associated formats that reflect the formatting changes made by the user using the output file editor 330. The second query may be performed on the database to extract a second relation, i.e., without artificially limiting the number of records retrieved as the user's field format preferences have been incorporated into the second query.
The communication module 345 may be configured to communicate the second relation including records having one or more fields with formats based on user input received through the output file editor 330 to one or more systems or devices for processing by an application thereon. The field format changes made by the user may be designed to ensure that the second relation is compatible with a client application running on another system or device.
Although
Computer program code for carrying out operations of data processing systems discussed above with respect to
Moreover, the functionality of the database management system server 115 of
The data processing apparatus of
Operations continue at block 410 where the output file editor module 330 receives user input to change the format of one or more fields of the records of the first relation.
Embodiments of the inventive subject matter may provide a mechanism for a user to extract data from a database for use, for example, by another application on a client device or system, which may have specific data format requirements, without needing expertise in writing command lines or queries to extract the data in the desired formats. This may provide reductions in both processor time spent in performing data extractions from a database that return errant formats and the memory consumed in storing such data extractions if they are not timely deleted thereby improving the operation of the host system. Moreover, by extracting the data from the database in the desired format(s) through the query of the database, both user and processor time may be saved (on a host system and/or a receiving client device/system) in correcting the format(s) in the extracted relation relative to alternatives approaches, such as processing the extracted relation to change the formats of one or more fields using an editor, tool, or other mechanism.
In the above-description of various embodiments of the present disclosure, aspects of the present disclosure may be illustrated and described herein in any of a number of patentable classes or contexts including any new and useful process, machine, manufacture, or composition of matter, or any new and useful improvement thereof. Accordingly, aspects of the present disclosure may be implemented entirely hardware, entirely software (including firmware, resident software, micro-code, etc.) or combining software and hardware implementation that may all generally be referred to herein as a “circuit,” “module,” “component,” or “system.” Furthermore, aspects of the present disclosure may take the form of a computer program product comprising one or more computer readable media having computer readable program code embodied thereon.
Any combination of one or more computer readable media may be used. The computer readable media may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium would include the following: a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an appropriate optical fiber with a repeater, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.
A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable signal medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.
Computer program code for carrying out operations for aspects of the present disclosure may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Scala, Smalltalk, Eiffel, JADE, Emerald, C++, C#, VB.NET, Python or the like, conventional procedural programming languages, such as the “C” programming language, Visual Basic, Fortran 2003, Peri, COBOL 2002, PHP, ABAP, dynamic programming languages such as Python, Ruby and Groovy, or other programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider) or in a cloud computing environment or offered as a service such as a Software as a Service (SaaS).
Aspects of the present disclosure are described herein with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the disclosure. It will be understood that each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable instruction execution apparatus, create a mechanism for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer readable medium that when executed can direct a computer, other programmable data processing apparatus, or other devices to function in a particular manner, such that the instructions when stored in the computer readable medium produce an article of manufacture including instructions which when executed, cause a computer to implement the function/act specified in the flowchart and/or block diagram block or blocks. The computer program instructions may also be loaded onto a computer, other programmable instruction execution apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable apparatuses or other devices to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide processes for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.
The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various aspects of the present disclosure. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems that perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The terminology used herein is for the purpose of describing particular aspects only and is not intended to be limiting of the disclosure. As used herein, the singular forms “a”, “an” and “the” are intended to include the plural forms as well, unless the context clearly indicates otherwise. It will be further understood that the terms “comprises” and/or “comprising,” when used in this specification, specify the presence of stated features, integers, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof. As used herein, the term “and/or” includes any and all combinations of one or more of the associated listed items. Like reference numbers signify like elements throughout the description of the figures.
The corresponding structures, materials, acts, and equivalents of any means or step plus function elements in the claims below are intended to include any disclosed structure, material, or act for performing the function in combination with other claimed elements as specifically claimed. The description of the present disclosure has been presented for purposes of illustration and description, but is not intended to be exhaustive or limited to the disclosure in the form disclosed. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the disclosure. The aspects of the disclosure herein were chosen and described in order to best explain the principles of the disclosure and the practical application, and to enable others of ordinary skill in the art to understand the disclosure with various modifications as are suited to the particular use contemplated.