아래가 테이블 압축 및 데이터 파일을 축소할 예정인 데이터베이스입니다.

전체 크기는 900GB 정도이고, 사용율을 90% 입니다.

개발 서버 용도로 사용할 예정이기 때문에,

상위 테이블들을 부담없이 테이블 압축 진행하려고 합니다.

테이블 압축 결과 40%의 여유 공간이 발생하였습니다.

디스크 공간을 줄이기 위해서 각 데이터 파일을 비워서 하나씩 제거 하려고 합니다.

8번 데이터 파일을 지우려 합니다.

데이터 파일을 비우는 작업

DBCC SHRINKFILE ('EFMS8', EMPTYFILE);

각 파일의 사용하는 Extent 를 확인 하는 방법

DBCC showfilestats

진행한 결과 아래와 같습니다.

이제 9번 파일 EFMS8 을 삭제해 보겠습니다.

ALTER DATABASE [EFMS_DEV]  REMOVE FILE [EFMS8]

9번 파일이 삭제되었습니다. 공간 확보를 위해서 위의 방법을 반복 진행하면 됩니다.

최종적으로 할당된 공간이 930GB --> 827GB로 줄어들었습니다.

아래와 같이 최종적으로 원하는 만큼 파일을 축소했습니다.

Leave a Comment

declare movetable cursor local fast_forward
for
	Select distinct A.name
	From sys.sysobjects AS A 
	INNER JOIN sys.indexes as i on A.id=i.object_id
	INNER JOIN sys.sysindexes AS B ON A.id = B.id
	INNER JOIN sys.sysfilegroups AS C ON B.groupid = C.groupid
	WHERE C.groupid=1 and a.id>100 and b.name is not null and A.xtype='U' 
    and a.uid=1 and i.type=1

open movetable
declare @table varchar(100)
fetch from movetable into @table

while @@fetch_status = 0
begin
	
	declare @sql varchar(1000)
	set @sql=''
	select @sql='CREATE UNIQUE CLUSTERED INDEX ['+name+'] on 
    ['+@table+']( ' from sys.indexes where type=1 and object_id=object_id(@table)

	SELECT @sql=@sql+'['+convert(varchar,c.name)+'] ASC,'
	FROM sys.indexes as i 
	join sys.objects as o on i.object_id=o.object_id
	join sys.index_columns as ic 
    on i.object_id=ic.object_id and i.index_id=ic.index_id
	join sys.columns as c on i.object_id=c.object_id and ic.column_id=c.column_id
	where i.type=1
    and o.schema_id=1
	and o.name =@table
	order by index_column_id

	set @sql=substring(@sql,0,len(@sql))
	set @sql=@sql+' ) WITH (DROP_EXISTING=ON,DATA_COMPRESSION=PAGE) ON FG_EFMS;'

	print @sql

	fetch from movetable into @table
end
close movetable
deallocate movetable

PRIMARY 파일 그룹에 있는 테이블 데이터를 FG_EFMS라는 파일 그룹으로 이동하려고 합니다.

 

PRINT 된 쿼리를 실행하면, 데이터가 FG_EFMS 파일 그룹으로 이동합니다.

 

아래 쿼리는 테이블이 어느 FILEGROUP에 있는지 확인

Select A.name
	, C.groupid
	, C.groupname
From sys.sysobjects AS A 
INNER JOIN sys.sysindexes AS B ON A.id = B.id
INNER JOIN sys.sysfilegroups AS C ON B.groupid = C.groupid

Leave a Comment

해당 내용의 원본 블로그 입니다.

https://blogs.sap.com/2015/02/05/simple-exercise-on-odata-and-sap-ui5-application-for-the-basic-crud-operation/

 

Simple Exercise on OData and SAP UI5 Application for the basic CRUD Operation | SAP Blogs

68 Likes 143,423 Views 67 Comments

blogs.sap.com

Contents

1. Introduction

2. Creation of OData Service

2.1 Creation of OData Service

2.1.1 Pre-requisites

2.1.2 Creation of Data Model

2.1.3 Generate Run time Objects

2.1.4 Service Implementation.

2.1.5 Registration of Service.

 

3.Consumption of OData Service SAP UI5 application.

3.1 Consuming OData Service for CRUD operation using UI5 Application.

3.1.1 Pre-requisites

3.1.2 Creation of SAP UI5 Application.

3.1.3 Create a Dashboard for Employee details display.

3.1.4 Pop-up window for Data Modify.

3.1.5 Create button for Data Creation.

4.Source code used.

List of Abbreviations/Acronyms

Term

Explanation

DPC

Data Provider Class

MPC

Model Provider Class

CRUD

Create, Read, Update, Delete

 

1. Introduction

In the demo we are going to see an example on SAP UI5 application for CRUD operation using SAP UI5 and OData Operations. This demo can be split in to two main divisions.

  • Creation of OData Service
  • Consuming an OData Service using SAP UI5 application.

2. Creation of OData Service

We are going to create an OData service for the custom Employee Details with the basic operations Create, Read, Delete and Update (CRUD).

2.1 Creation of OData Service

In this example we are going to see the creation of OData service using the SAP Gateway Service Builder(SEGW). The OData Service can also be built by various ways available and we took the Gateway Service Builder approach here. In this example, the OData has been deployed in the embedded deployment model where the single system acts as both Gateway and Backend system.

The below mentioned are the steps are used to create the OData service.

  • Pre-requisite
  • Creation of Data Model
  • Generate Runtime Objects
  • Registration of Service
  • Service Implementation

2.1.1 Pre-requisites

In the pre-requisite, we are going to create a transparent table which is going to act as a back end table and function module for the Create, Read, Update and Delete (CRUD) operation to be used in this OData creation.

1. Creation of Table, we will create a Z table with basic employee details. Once the table is created, please create some entries. Later, these entries will be displayed in the UI dashboard.

2. Create Function Modules for Create, Read, Update and Delete operation.

Important Note: Create the Function Module as Remote-Enabled Function Module.

  • Function Module for Create Operation

  • Function Module for Read Operation

  • Function Module for Update Operation

  • Function Module for Delete Operation

2.1.2 Creation of Data Model

The Data model is used to describe the OData Services which contains Entity, Properties, and EntitySet and so on.

In this example, we are going to build a OData service using SAP Gateway Service Builder (SEGW)

1. Go to Transaction Code: SEGW, which will land you in the Gateway Service Builder.

2. Create a Project

3. Once the Project is created, create Entity and EntitySet by the Import option available

4. Give the Entity, Structure and the EntitySet create option as below. We are going to use the already created employee table structure here.

5. Select all the Data source parameters and press Next.

6. Press Finish to complete the Data Model creation.

Note: There are number of ways to define the Data Model and we have chosen the import->DDIC structure option here.

7. The Entity and EntitySet are created now, and we can see the entity type has been created with reference to the given ABAP Structure.

8. Once it is added, we have the properties created now as below. Choose Empid as a Key here, the key also can be defined at the step 6 by marking the key field as “Is Key”.

2.1.3 Generate Run time Objects

1. Once all the above activities are completed, generate the run time objects by pressing the Ctrl+F3 button. This will pop up the below window with all the option to generate the run time objects.

2. Once this activity is complete, we have the objects generated as below.

This will generate the Data Provider Class, widely we call it as DPC and Model Provider Class and this we call it as MPC class.

DPC=> Business Logic for CRUD operation goes here.

MPC=>Data Model Class, where we can see the data model defined.

2.1.4 Service Implementation

In this section, we are going to generate the Business logic using the RFC Function Module we have created earlier for the CRUD Operations. Instead of using RFC Function Module, we can also use Business Object Repository. Also you can use your own custom logic in the DPC_EXT classes for the data operations.

1. Go to Service Implementation, select the Create Operation and go to Map to Data Source.

2. Select the Function Module for Create Option as below and press Continue

3. Then the below window will be open with the Data Source Parameter and the Data Mapping has to be done as follows. Also we have an option “Propose Mapping”, which does the data mapping automatically.

Create

Drag and drop the Data Source Parameter as shown below for the Mapping Operation.

Similarly, we have defined the mapping for Delete, GetEntity, GetEntitySet and Update operation using the Function Modules we have already created.

Delete

GetEntity

We have added one more Property, Empid to fetchthe records from the backend based on the Key.

GetEntitySet

We have added one more Property, Empid to fetch the records from the backend based on the Key.

Update

Once the mapping is completed, generate run time objects

( Ctrl+F3 ) and go to your Data Provider Class(DPC) to find the ABAP coding has been generated automatically.

Once the above all activities are completed, we have to register the OData service to consume them.

2.1.5 Registration of Service

1. Go to gateway and select the Gateway service. You will find the OData service is yet to be registered as the Registration status traffic light button is grey.

2. Select the Service and press the Register

button to complete the Registration of the OData service and press yes to continue.

3. Select the package assignment as local object and continue the process.

4. On press of continue will register the OData Service. You can also add the service using the Transaction code: /IWFND/MAINT_SERVICE.

5. Select the Gateway Client and this will launch you in the SAP Netweaver Gateway Client. This can also be accessed separately using the T-code : /IWFND/GW_CLIENT. Execute the uri: /sap/opu/odata/sap/ZMM_EMP_SRV/$metadata to test the OData service.

By this successful response, the OData service has been built and it is working fine. Further we will see, how can we consume the OData service using SAP UI5 application in the next chapter.

Metadata Uri: http://:/sap/opu/odata/sap/ZMM_EMP_SRV/$metadata : OData Service Metadata document can be accessed here.

Entity Set : http://:/sap/opu/odata/sap/ZMM_EMP_SRV/EmployeeSet : The Employee records can be accessed here.

Replace the tags , with your server and port details.

3. Consumption of OData Service using SAP UI5 application

In this example, we will create a SAPUI5 application for Employee and we will see the see how the OData service can be consumed for the basic CRUD operation. Whereas the below are the CRUD methods and their equivalent HTTP methods in the OData service.

3.1 Consuming OData Service for CRUD Operation using SAP UI5 Application

In this example, we are going to create a UI5 application by which we can consume the already created OData service for the basic CRUD operation.

This app contains the following details:

  • Pre-requisite
  • Creation of SAP UI5 Application
  • Create a Dashboard to display Employee Details
  • Popup window for Data modify operation
  • Create button for Data creation

3.1.1 Pre-requisites

We need an Eclipse IDE with version Kepler, Luna or any other latest version with the SAP UI5 plugin to develop the SAP UI5 application. If you want to know how the SAP UI5 development plugins can be added to eclipse, click here.

3.1.2 Creation of SAP UI5 Application

Create a New SAP UI5 project by the below path in the menu bar

1. Go to Menu bar : File->New->Other->SAP UI5 Application development(Application Project) and then press next.

 

2. Give the project name, with the option to create a Initial view. The view can be created separately as well.

 

3. Provide the view name and choose the Paradigm as JavaScript and press Finish.

 

4. By this, we have created an empty SAPUI5 project called “EmpCRUD” with view “EmpDetails” as below.

3.1.3 Create a Dashboard for Employee details display

1. Create a dashboard with the Table contains fields as Employee Id, Name, Address and Designation. Assign the same to the Page and return the page. (EmpDetails.view.js)

 

2. You can execute and view the dashboard the option “Web App Preview” as below. Also you can use the option “Run on Server”, but you need to install Tomcat / any other web server for the same.

 

3. Then it appears in the Eclipse as below. Further you can copy and paste the url in the chrome browser. You can use any browser, but I have used chrome as the debugging in bit easy.

 

4. In the browser, it appears as below. An empty Dashboard is built now.

 

5. Now the Dashboard is built, we will see how the data can be populated here. Go to “EmpDetails.controller.js” and in the onInit function, write the coding as below to fetch the data from server. This will do a OData call to fetch the data from the server in JSON format. This data further has to be bind to the table we built already. Replace the tags , with your respective server details.

ZMM_EMP_SRV: Name of the OData Service, we already built.

EmployeeSet: Name of the entity set name for employees.

 

6. The data binding happens as below.

 

Run the app again in the “Web App Preview” to see the data displayed in the dashboard as below. When prompted for login credentials to your backend server, provide your User id/Password credentials to continue.

7. When you run/execute the application, it may not run due to ‘Access-Control-Allow-Origin’ issue. To overcome this issue, we have an option available with chrome browser.

  • Make sure you close out all instances of Google Chrome including ending all its processes in the Windows Task Manager.
  • Right-Click on your Google Chrome Icon, Go to the Target: and add the command “–disable-web-security”, apply the settings as below.
  • This should allow you to overcome the ‘Access-Control-Allow-Origin’ issue and the application should run in the chrome browser.

8.When executed again, the employee data appears in the chrome browser as below.

 

By this READ (GET) operation is complete. Further we will see the remaining operations below.

3.1.4 Pop-up window for Data Modify

1. We will create a popup window with text areas for Employee Id, Emp Name, Address and Designation with three buttons Update, Delete and Cancel for the modify operations. Go to View.js and add the coding as below.

 

2. We have the dialog box or the pop-up window created now. Further we will see how we can make use of the dialog box for the modify operation.

On the press of employee details, we will open a dialog box where we will show the employee details and it can be modified or deleted. We will read the employee details from the selected item and will populate the dialog box. The key, “Employee id” we will disable for edit.

 

3. Once it is done, re -run the application to see a dialog window pop-up on a selection of the item. The selected record details are shown now. But the buttons “Update”, ”Delete” and “Cancel” actions are yet to be defined.

 

4. Go to the controller.js. where you can define the action of the Update button.

 

5. GET in the OData call is used to fetch the X-CSRF token. Once the token is fetched, it act as an authentication to do the further update(PUT) operation.

Note: You need a CSRF token to be fetched to perform the Modify/Insert operation. To know more about the CSRF token click here.

Re-run the application to test the update operation. I have updated the address and pressed update button.

6. On the success, we will reload the main page with the newly updated details.

 

7. On the press of Ok button, the page will be re-loaded with the newly updated details.

 

The update operation is complete. We will further see the Delete operation details.

8. Go to the controller.js. where we can define the action of the Delete button.

 

9. GET in the OData call is used to fetch the X-CSRF token. Once the token is fetched, it act as an authentication to do the further delete(DELETE) operation.

Note: You need a CSRF token to be fetched to perform the Modify/Insert operation. To know more about the CSRF token click here.

Re-run the application to test the delete operation. I have selected a record and pressed delete button.

10. On the success, we will reload the main page with the deleted details.

 

11. On the press of Ok button, the page will be re-loaded with the modified details.

 

12. The Delete operation is complete. On the press of the cancel button we will close the dialog box by adding the below in the controller.js

 

3.1.5 Create button for Data Creation

1. We will place a button “Create New Employee” in the dashboard and by this we will see the creation of a new employee.

Go to view.js where we will create button Submit, Save. We will add the Save button to the Dialog box.

 

2. We will add the button Submit button to the main dashboard as below.

 

3. Further we will go to controller.js to do the action for the “Create New Employee”. We will use the same dialog box, we have already created for the modify operation. We will hide the “Save” button in the modify operation and while creation, we will hide the “Update”, “Delete” button.

 

4. Re-run the application to see the new button “Create New Employee” visible in the main page and on the press of the button, a dialog box with the employee details can be added.

 

5. While press on the save, nothing will happen as we are yet to write the function for the “Save” action.

Go to the controller.js, where we can define the action for the “Save” button.

 

6. GET in the OData call is used to fetch the X-CSRF token. Once the token is fetched, it act as an authentication to do the further Create(POST) operation.

Note: You need a CSRF token to be fetched to perform the Modify/Insert operation. To know more about the CSRF token click here.

Re-run the application to test the Create operation.

7. On the success, we will reload the main page with the newly created details.

 

8. On the press of Ok button, the page has been re-loaded with the newly created details.

 

The Create operation is complete. By then we have completed all the basic CRUD operations.

4. Source code used

The source code used in this project is also given as a reference here.

Index.html

<!DOCTYPE HTML>
<html>
    <head>
        <meta http-equiv=“X-UA-Compatible” content=“IE=edge”>
        <meta http-equiv=‘Content-Type’ content=‘text/html;charset=UTF-8’/>
        <script src=“resources/sap-ui-core.js”
            id=“sap-ui-bootstrap”
            data-sap-ui-libs=“sap.m”
            data-sap-ui-theme=“sap_bluecrystal”>
        </script>
        <!– only load the mobile lib “sap.m” and the “sap_bluecrystal” theme –>
        <script>
            sap.ui.localResources(“empcrud”);
            var app = new sap.m.App({initialPage:“idEmpDetails1”});
            var page = sap.ui.view({id:“idEmpDetails1”, viewName:“empcrud.EmpDetails”,
            type:sap.ui.core.mvc.ViewType.JS});
            app.addPage(page);
            app.placeAt(“content”);
        </script>
    </head>
    <body class=“sapUiBody” role=“application”>
        <div id=“content”></div>
    </body>
</html>

EmpDetails.view.js

sap.ui.jsview(“empcrud.EmpDetails”, {
    /** Specifies the Controller belonging to this View.
    * In the case that it is not implemented, or that “null” is returned, 
    * this View does not have a Controller.
    * @memberOf empcrud.EmpDetails
    */
    getControllerName : function() {
                return “empcrud.EmpDetails”;
    },
    /** Is initially called once after the Controller has been instantiated. 
    * It is the place where the UI is constructed.
    * Since the Controller is given to this method, its event handlers 
    * can be attached right away.
    * @memberOf empcrud.EmpDetails
    */
    createContent : function(oController) {
		var oPage = new sap.m.Page({
			title: “Employee Details”,
		});
		var oBtnUpd = new sap.m.Button(“Update”, {
			text: “Update”,
			tap: [ oController.Update, oController ]
    });
    var oBtnDel = new sap.m.Button(“Delete”, {
		text: “Delete”,
		tap: [ oController.Delete, oController ]
		});                                 
    var oBtnCan = new sap.m.Button(“Cancel”, {
        text: “Cancel”,
        tap: [ oController.Cancel, oController ]
        });
    var oBtnSub = new sap.m.Button(“Submit”, {
        text: “Create New Employee”,
        press: oController.NewEntry, 
        });                 
    var oBtnSav = new sap.m.Button(“Save”, {
        text: “Save”,
        tap: [ oController.Save, oController ]
        });
// Dialog box / pop-up window for Add/Modify Employee Data              
	var oDialog = new sap.m.Dialog(“Dialog”,{
		title:“Add/Modify Employee”,
		modal: true,
		contentWidth:“1em”,
		content:[
		new sap.m.Label({text:“Enter Emp Id(must be a number)”}),
		new sap.m.Input({                                       
		maxLength: 20,
		id: “Id”
		}), 
		new sap.m.Label({text:“Enter Name”}),
		new sap.m.Input({                                       
		maxLength: 20,  
		id: “Name”
		}),
		new sap.m.Label({text:“Enter Address”}),
		new sap.m.Input({                                       
		maxLength: 20,  
		id: “Address”
		}),                                 
		new sap.m.Label({text:“Enter Designation”}),
		new sap.m.Input({                                       
		maxLength: 20,
		id: “Role”
		}),oBtnUpd, oBtnDel, oBtnCan, oBtnSav
		]
		});        
// Table or Dashboard to show the Employee Data                         
    var oTable = new sap.m.Table({
        id: “Employees”,
        itemPress : [ oController.ItemPress,oController ],
        columns: [
        new sap.m.Column({
        width: “1em”,
        header: new sap.m.Label({
        text: “Emp ID”  }) }),
        new sap.m.Column({
        width: “1em”, 
        header: new sap.m.Label({
        text: “Name” })
        }),
        new sap.m.Column({
        width: “1em”, 
        header: new sap.m.Label({
        text: “Address”
        })
        }),
        new sap.m.Column({   
        width: “1em”,
        header: new sap.m.Label({
        text: “Designation”
        })
        })
        ]  
    });
// Template  to map the data to the respective column       
var template = new sap.m.ColumnListItem({
    id: “first_template”,
    type: “Navigation”,
    visible: true,
    cells: [
    new sap.m.Label(“ID”, {
    text: “{Empid}”
        }),
    new sap.m.Label({
    text: “{Empname}”
            }),
    new sap.m.Label({
    text: “{Empadd}”
        }),
    new sap.m.Label({
    text: “{Empdes}”
    })
    ]       
 });
var  oFilters = null;
 oTable.bindItems( “/results”,template, null, oFilters);       
 oPage.addContent(oTable);
 oPage.addContent(oBtnSub);
	return oPage;               
		}
});

 

EmpDetails.controller.js

sap.ui.controller(“empcrud.EmpDetails”, {
/**
* Called when a controller is instantiated and its 
* View controls (if available) are already created.
* Can be used to modify the View before it is displayed, 
* to bind event handlers and do other one–time initialization.
* @memberOf empcrud.EmpDetails
*/
	onInit: function() {
		var sServiceUrl = “http://<host name>:<port no>/sap/opu
        /odata/sap/ZMM_EMP_SRV”;
		var oModel = new sap.ui.model.odata.ODataModel(sServiceUrl,true);
		var oJsonModel = new sap.ui.model.json.JSONModel();
oModel.read(“/EmployeeSet?”,null,null,true,function(oData,repsonse){
    oJsonModel.setData(oData);
});    
sap.ui.getCore().setModel(oJsonModel);
    },
/**
* Similar to onAfterRendering, but this hook is invoked before the controller’s 
* View is re–rendered
* (NOT before the first rendering! onInit() is used for that one!).
* @memberOf empcrud.EmpDetails
*/
//          onBeforeRendering: function() {
//
//          },
/**
* Called when the View has been rendered (so its HTML is part of the document).
* Post–rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
* @memberOf empcrud.EmpDetails
*/
//          onAfterRendering: function() {
//
//          },
/**
* Called when the Controller is destroyed. Use this one to free resources and 
* finalize activities.
* @memberOf empcrud.EmpDetails
*/
//          onExit: function() {
//
//          }
	ItemPress: function(evt) {
		sap.ui.getCore().byId(“Dialog”).open();                     
		sap.ui.getCore().byId(“Update”).setVisible(true);
		sap.ui.getCore().byId(“Delete”).setVisible(true);
		var oSelectedItem = evt.getParameter(“listItem”);
		var sID = oSelectedItem.getBindingContext().getProperty(“Empid”);
		var sName = oSelectedItem.getBindingContext().getProperty(“Empname”);
		var sAddr = oSelectedItem.getBindingContext().getProperty(“Empadd”);
		var sRole = oSelectedItem.getBindingContext().getProperty(“Empdes”);
		sap.ui.getCore().byId(“Id”).setValue(sID);
		sap.ui.getCore().byId(“Name”).setValue(sName);
		sap.ui.getCore().byId(“Address”).setValue(sAddr);
		sap.ui.getCore().byId(“Role”).setValue(sRole);
		sap.ui.getCore().byId(“Id”).setEnabled(false);
		},
		NewEntry: function() {
		sap.ui.getCore().byId(“Dialog”).open();
		sap.ui.getCore().byId(“Save”).setVisible(true);
		sap.ui.getCore().byId(“Update”).setVisible(false);
		sap.ui.getCore().byId(“Delete”).setVisible(false);
		sap.ui.getCore().byId(“Id”).setValue(“”);
		sap.ui.getCore().byId(“Name”).setValue(“”);
		sap.ui.getCore().byId(“Address”).setValue(“”);
		sap.ui.getCore().byId(“Role”).setValue(“”);                 
		sap.ui.getCore().byId(“Id”).setEnabled(true);
		},                      
Save: function() {
	var oEntry = {};
	oEntry.Empid= sap.ui.getCore().byId(“Id”).getValue();
	oEntry.Empname= sap.ui.getCore().byId(“Name”).getValue();
	oEntry.Empadd= sap.ui.getCore().byId(“Address”).getValue();
	oEntry.Empdes= sap.ui.getCore().byId(“Role”).getValue();    
	OData.request({
		requestUri : “http://<host name>:<port no>/sap/opu/
        odata/sap/ZMM_EMP_SRV
        /EmployeeSet”,
		method : “GET”,
		headers : {
				“X-Requested-With” : “XMLHttpRequest”,
				“Content-Type” : “application/atom+xml”,
				“DataServiceVersion” : “2.0”,
				“X-CSRF-Token” : “Fetch”
				}
			},
			function(data, response) {
				header_xcsrf_token = response.
                headers[‘x-csrf-token’];
				var oHeaders = {
					“x-csrf-token” : header_xcsrf_token,
					‘Accept’ : ‘application/json’,
			};
		OData.request({
				requestUri : “http://<host name>:<port no>
                /sap/opu/odata/sap/
                ZMM_EMP_SRV/EmployeeSet”,
				method : “POST”,
				headers : oHeaders,
				data:oEntry
			},
				function(data,request) {
				alert(“Employee Created Successfully”);         
				location.reload(true);
			},          
				function(err) {
				alert(“Employee Creation Failed”);
			});
		}, function(err) {
			var request = err.request;
			var response = err.response;
			alert(“Error in Get — Request “ + request +
            ” Response “ + response);
			});                                               
Update: function() {
	var oEntry = {};
	oEntry.Empid= sap.ui.getCore().byId(“Id”).getValue();
	oEntry.Empname= sap.ui.getCore().byId(“Name”).getValue();
	oEntry.Empadd= sap.ui.getCore().byId(“Address”).getValue();
	oEntry.Empdes= sap.ui.getCore().byId(“Role”).getValue();    
	OData.request({
		requestUri : “http://<host name>:<port no>/sap/opu
        /odata/sap/ZMM_EMP_SRV/
        EmployeeSet”,
		method : “GET”,
		headers : {
				“X-Requested-With” : “XMLHttpRequest”,
				“Content-Type” : “application/atom+xml”,
				“DataServiceVersion” : “2.0”,
				“X-CSRF-Token” : “Fetch”
				}
			},
			function(data, response) {
				header_xcsrf_token = response.
                headers[‘x-csrf-token’];
				var oHeaders = {
					“x-csrf-token” : header_xcsrf_token,
					‘Accept’ : ‘application/json’,
			};
		OData.request({
				requestUri : “http://<host name>:<port no>
                /sap/opu/odata/sap/
                ZMM_EMP_SRV/EmployeeSet(‘”+oEntry.Empid+“‘)”,
				method : “PUT”,
				headers : oHeaders,
				data:oEntry
			},
				function(data,request) {
				alert(“Update Success”);            
				location.reload(true);
			},          
				function(err) {
				alert(“Update Failed”);
			});
		}, function(err) {
				var request = err.request;
				var response = err.response;
				alert(“Error in Get — Request “ + request +
                ” Response “ + response);
			});         
			},
// Delete Action                                            
Delete: function() {
            var oEntry = {};
            oEntry.Empid= sap.ui.getCore().byId(“Id”).getValue();
            OData.request({
				requestUri : “http://<host name>:<port no>/sap
                /opu/odata/sap/
                ZMM_EMP_SRV/EmployeeSet(‘” + oEntry.Empid + “‘)”,
				method : “GET”,
				headers : {
						“X-Requested-With” : “XMLHttpRequest”,
						“Content-Type” : “application/atom+xml”,
						“DataServiceVersion” : “2.0”,
						“X-CSRF-Token” : “Fetch”
						}
					},
					function(data, response) {
						header_xcsrf_token = response.
                        headers[‘x-csrf-token’];
						var oHeaders = {
							“x-csrf-token” : header_xcsrf_token,
							‘Accept’ : ‘application/json’,
					};
				OData.request({
						requestUri : “http://<host name>:<port no>
                        /sap/opu/odata/
                        sap/ZMM_EMP_SRV/EmployeeSet(‘”+oEntry.Empid+“‘)”,
						method : “DELETE”,
						headers : oHeaders,
						data:oEntry
					},
						function(data,request) {
						alert(“Delete Success”);            
						location.reload(true);
					},          
						function(err) {
						alert(“Delete Failed”);
					});
				}, function(err) {
						var request = err.request;
						var response = err.response;
						alert(“Error in Get — Request “ + request + ” 
                        Response “ + response);
					});         
				},
// Cancel Action                          
  Cancel:function() {
    sap.ui.getCore().byId(“Dialog”).close();
     }
})

 

 

'SAP > ABAP' 카테고리의 다른 글

Simple Exercise on OData and SAP UI5 Application for the basic CRUD Operation  (0) 2019.07.05
SAP Gateway and OData  (0) 2019.07.05

Leave a Comment

SAP Gateway and OData

Use

 

By exposing SAP Business Suite functionality as REST-based OData (Open Data Protocol) services, SAP Gateway enables SAP applications to share data with a wide range of devices, technologies, and platforms in a way that is easy to understand and consume.

SAPGateway는 SAPBusinessSuite기능을 REST기반 OData(OpenDataProtocol)서비스로 노출함으로써 SAP애플리케이션이 이해하기 쉽고 이해하기 쉬운 방식으로 다양한 장치, 기술 및 플랫폼과 데이터를 공유합니다.

 

Using REST services provides the following advantages:

REST서비스를 사용하면 다음과 같은 이점이 있습니다.

  • Obtain human readable results; you can use your browser to see what data you will get.

  • Use stateless applications

  • Receive related pieces of information, one leading to another.

  • Use standard GET, PUT, POST, DELETE, and QUERY. If you know where to GET data, you know where to PUT it, and you can use the same format.

  • 사람이 읽을 수 있는 결과를 얻습니다. 브라우저를 사용하여 어떤 데이터를 얻을지 볼 수 있습니다.

  • 상태 비저장 애플리케이션 사용

  • 다른 정보로 이어지는 관련 정보를 수신합니다.

  • 표준 GET, PUT, POST, Delete및 QUERY를 사용합니다. 데이터를 가져올 위치를 알고 있으면 PUT를 실행할 위치를 알고 동일한 형식을 사용할 수 있습니다.

What Is OData and Why Do We Use It?

OData란 무엇이고 왜 우리는 그것을 사용하는가?

 

OData is a Web protocol for querying and updating data, applying and building on Web technologies such as HTTP, Atom Publishing Protocol (AtomPub), and RSS (Really Simple Syndication) to provide access to information from a variety of applications. It is easy to understand and extensible, and provides consumers with a predictable interface for querying a variety of data sources.

OData는 HTTP, Atompub(AtomPublishingProtocol)및 RSS(ReallySimpleSyndication)와 같은 웹 기술을 기반으로 데이터를 쿼리 및 업데이트하는 웹 프로토콜입니다. 이는 이해하기 쉽고 확장 가능하며 소비자에게 다양한 데이터 소스를 쿼리 하기 위한 예측 가능한 인터페이스를 제공합니다.

 

AtomPub is the standard for treating groups of similar information snippets as it is simple, extensible, and allows anything textual in its content. However, as so much textual enterprise data is structured, there is also a requirement to express what structure to expect in a certain kind of information snippet. As these snippets can come in large quantities, they must be trimmed down to manageable chunks, sorted according to ad-hoc user preferences, and the result set must be stepped through page by page.

Atompub는 단순하고 확장 가능하며 내용에 텍스트를 허용하기 때문에 유사한 정보 조각 그룹을 처리하기 위한 표준입니다. 그러나 텍스트 기업 데이터가 구조화되어 있기 때문에 특정 유형의 정보 조각에서 기대할 구조를 표현할 필요가 있다. 이러한 조각은 대량으로 제공될 수 있으므로 관리 가능한 조각으로 잘라 임시 사용자 환경 설정에 따라 정렬해야 하며, 결과 세트는 페이지 단위로 단계별로 정리해야 합니다.

 

OData provides all of the above as well as additional features, such as feed customization that allows mapping part of the structured content into the standard Atom elements, and the ability to link data entities within an OData service (via "…related…" links) and beyond (via media link entries). This facilitates support of a wide range of clients with different capabilities:

OData는 구조화된 콘텐츠의 일부를 표준 Atom요소에 매핑 할 수 있는 피드 사용자 지정,"관련된…"서비스를 통해 OData서비스 내의 데이터 엔티티를 링크하는 기능과 같은 추가 기능과 함께 위의 모든 기능을 제공합니다. 이를 통해 다양한 기능을 갖춘 광범위한 클라이언트를 지원할 수 있습니다.

  • Purely Atom, simply paging through data.

  • Hypermedia-driven, navigating through the data web.

  • Aware of query options, tailoring the OData services to their needs.

  • 그냥 Atom, 단순히 데이터를 페이징만 하면 됩니다.

  • 하이퍼 미디어를 기반으로 데이터 웹 탐색

  • 쿼리 옵션을 인식하고 필요에 따라 OData서비스를 조정합니다.

OData is also extensible, like the underlying AtomPub, and thereby allows the addition of features that are required when building easy-to-use applications, both mobile and browser-based.

OData는 기본 Atompub과 같이 확장 가능하며, 따라서 모바일 및 브라우저 기반 애플리케이션을 구축할 때 필요한 기능을 추가할 수 있습니다.

 

OData for SAP Products

 

SAP Gateway uses OData for SAP Products, which contains SAP-specific metadata that helps the developer to consume SAP business data, such as descriptions of fields that can be retrieved from the SAP ABAP Dictionary. The following are examples of OData for SAP applications:

SAPGateway는 SAPABAP사전에서 검색할 수 있는 필드 설명과 같은 SAP비즈니스 데이터를 사용할 수 있도록 개발자에게 도움이 되는 SAP제품용 OData를 사용합니다. 다음은 SAP애플리케이션용 OData의 예입니다.

  • Human-readable, language-dependent labels for all properties (required for building user interfaces).

  • Free-text search, within collections of similar entities, and across collections using OpenSearch. OpenSearch can use the Atom Syndication Format for its search results, so the OData entities that are returned by the search fit in, and OpenSearch can be integrated into AtomPub service documents via links with rel="search", per collection as well as on the top level. The OpenSearch description specifies the URL template to use for searching, and for collections it simply points to the OData entity set, using a custom query option with the name of "search".

  • Semantic annotations, which are required for applications running on mobile devices to provide seamless integration into contacts, calendar, and telephony. The client needs to know which OData properties contain a phone number, a part of a name or address, or something related to a calendar event.

  • Not all entities and entity sets will support the full spectrum of possible interactions defined by the uniform interface, so capability discovery will help clients avoiding requests that the server cannot fulfill. The metadata document will tell whether an entity set is searchable, which properties may be used in filter expressions, and which properties of an entity will always be managed by the server.

  • Most of the applications for "light-weight consumption" follow an interaction pattern called "view-inspect-act", "alert-analyze-act", or "explore & act", meaning that you somehow navigate (or are led) to an entity that interests you, and then you have to choose what to do. The chosen action eventually results in changes to this entity, or entities related to it, but it may be tricky to express it in terms of an Update operation, so the available actions are advertised to the client as special atom links (with an optional embedded simplified "form" in case the action needs parameters) and the action is triggered by POSTing to the target URI of the link.

  • 모든 특성에 대한 사람이 읽을 수 있는 언어 의존적 라벨(사용자 인터페이스를 구축하는 데 필요).

  • Open/Search를 사용하여 유사한 엔티티 모음 내 및 컬렉션 간에 자유 텍스트 검색을 수행합니다. OpenSearch는 검색 결과에 AtomSyndication형식을 사용할 수 있으므로 검색에서 반환되는 OData엔터티와 함께 AtomSyndication형식을 사용합니다. OpenSearch설명은 검색에 사용할 URL템플릿을 지정하며,"검색"이름의 사용자 지정 쿼리 옵션을 사용하여 OData엔티티 집합을 가리키기만 하면 됩니다.

  • 시멘틱 주석-모바일 기기에서 실행 중인 애플리케이션이 연락처, 일정 및 전화 통신에 원활하게 통합되는 데 필요합니다. 클라이언트는 전화 번호, 이름 또는 주소의 일부 또는 일정 관리 이벤트와 관련된 정보를 포함하는 OData속성을 알아야 합니다.

  • 모든 엔티티와 엔티티 집합이 동일한 인터페이스에 의해 정의된 가능한 상호 작용의 전체 스펙트럼을 지원하는 것은 아니므로, 기능 검색은 클라이언트가 서버가 수행할 수 없는 요청을 피하도록 도와 줍니다. 메타 데이터 문서는 엔티티 집합이 검색 가능한지 여부, 필터 식을 사용할 수 있는 속성, 그리고 항상 서버에서 관리할 엔티티의 속성을 알려 줍니다.

  • "경량 소비"를 위한 대부분의 애플리케이션은 "뷰-검사-조치","모든 것을 분석하고"&를 탐색하는 "상호 작용 패턴을 따릅니다. 선택한 액션은 결국 이 엔티티 또는 이와 관련된 엔티티를 변경하지만 업데이트 작업으로 표현하기가 까다로울 수 있으므로 사용 가능한 액션은 "단순화된 선택적"의 클라이언트에 알림 링크의 대상 URI로 이동하는 중입니다.

The following simplified diagram shows how Atom, OData, and OData for SAP Products fit together:

다음의 간단한 다이어그램은 SAP제품의 Atom, OData및 OData가 어떻게 서로 맞는지를 보여 줍니다.

For more information about OData, see http://www.odata.org

 

OData - the Best Way to REST

OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. OData helps you focus on your business logic while building RESTful APIs without having to worry about

www.odata.org

 

'SAP > ABAP' 카테고리의 다른 글

Simple Exercise on OData and SAP UI5 Application for the basic CRUD Operation  (0) 2019.07.05
SAP Gateway and OData  (0) 2019.07.05

Leave a Comment

JAVA 환경 구성

자바 기반의 이클립스를 실행하기 위해서 자바 (Java Runtime Environment)환경을 설치합니다.

 

오라클 자바 홈페이지를 통해서 JDK를 다운로드 받습니다.

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

 

Java SE Development Kit 8 - Downloads

Java SE Development Kit 8 Downloads Thank you for downloading this release of the Java™ Platform, Standard Edition Development Kit (JDK™). The JDK is a development environment for building applications, applets, and components using the Java programming la

www.oracle.com

설치한 후 cmd 창에서 java -version 명령어를 통해 java 버전 확인이 가능합니다.

JRE 버전 확인

 

이클립스 설치

이클립스 홈페이지를 통해서 eclipse 를 다운로드 합니다.

http://www.eclipse.org/downloads/packages/

 

Eclipse Packages | The Eclipse Foundation - home to a global community, the Eclipse IDE, Jakarta EE and over 350 open source pro

444 MB 850 DOWNLOADS The Modeling package provides tools and runtimes for building model-based applications. You can use it to graphically design domain models, to leverage those models at design time by creating and editing dynamic instances, to collabora

www.eclipse.org

클립스 버전에 따른 SAP Development Tools 지원 내용은 아래 내용에서 확인하실 수 있습니다.

SAP Development Tools

https://tools.hana.ondemand.com/

 

SAP Development Tools

This site allows you to install various SAP development tools. The downloads are provided under the terms of the SAP DEVELOPER LICENSE AGREEMENT. Eclipse Software Sites To install some or all of the Eclipse tools, get an installation compatible with the de

tools.hana.ondemand.com

이클립스의 경우 Install 과정없이 압축을 풀고 eclipse.exe를 실행하시면 됩니다.

 

이클립스에서 SAP Hana studio plugin 설치

이클립스를 실행하고 Help > Install New Software 에 SAP Developement Tools Site 정보를 넣어서 설치를 진행합니다.

 

For Eclipse 2018-12 (4.10), use https://tools.hana.ondemand.com/2018-12

For Eclipse 2018-09 (4.9), use https://tools.hana.ondemand.com/2018-09

For Eclipse Photon (4.8), use https://tools.hana.ondemand.com/photon

For Eclipse Oxygen (4.7), use https://tools.hana.ondemand.com/oxygen

 

목적이 SAP HANA Tools을 사용하기 위함이기 때문에

https://tools.hana.ondemand.com/2018-12 를 넣고 SAP HANA Tools를 선택합니다.

설치 항목 선택

인스톨 진행 상황은 우측 하단에서 확인이 가능합니다.

 

HANA Database 접속

Windows > Perspective > Open Perspective > Other를 선택하고 Hana Modeler를 선택합니다.

Open Perspective

 

SAP HANA Modeler 오픈

 

HANA Modeler 선택 및 시스템 등록

 

시스템 접속 정보

 

사진 설명을 입력하세요.

HANA Database 이외에도 ABAP 개발환경도 동일하게 설치하여 사용하실 수도 있습니다.

 

 

'SAP > HANADB' 카테고리의 다른 글

이클립스에서 SAP hana studion plugin 설치  (0) 2019.07.04
SAP HANA DBA 서비스 확인  (0) 2019.06.30

Leave a Comment

SAP ERP의 IDOC 문서 관련된 테이블들이 커지므로 인하여

데이터베이스 공간과 성능 저하에 요인이 될 수 있습니다.

그래서 해당 테이블을 아카이브 하려고 합니다.

 

SAP TCODE WE11에서 프로그램으로 삭제 가능합니다.

https://apps.support.sap.com/sap/support/knowledge/preview/en/1574016

 

1574016 - Deleting idocs with WE11/ RSETESTD | SAP Knowledge Base Article

Symptom Delete IDOCS - WE11 /  RSETESTD EDI* Tables are too big Poor performance when scanning idoc tables Deleting old idocs Cleanup EDI tables Read more... Environment SAP Basis release 620 and higher Product SAP NetWeaver Keywords RSETESTD, WE11, idoc,

apps.support.sap.com

SAP 프로그램으로 삭제하는 것이 데이터의 참조값들을 확인해서 깔끔하게 삭제할 수 있습니다.

그러나 시간이 오래 걸리는 단점이 있습니다.

그래서 DB에서 삭제하려고 합니다.

TCODE DB15에서 아카이브오브젝트와 테이블관계를 확인해 보면,

IDOC 아카이브 오브젝트에 EDI30C, EDI40, EDIDC, EDIDS 테이블들이

포함되어 있는 것을 확인할 수 있습니다.

이를 기반으로 EDI30C, EDI40, EDIDC,EDIDS 테이블을 직접 삭제하려고 합니다.

DECLARE @TABLE TABLE(MANDT NVARCHAR(3), DOCNUM NVARCHAR(16))

WHILE EXISTS (SELECT TOP 1 MANDT FROM fph.EDIDS WHERE MANDT='100' 
AND LOGDAT<'20190101')
BEGIN
	INSERT INTO @TABLE
	SELECT TOP 10000 MANDT,DOCNUM FROM fph.EDIDS WHERE MANDT='100' 
AND LOGDAT<'20190101'

	DELETE fph.EDI40
	FROM fph.EDI40 AS T
	JOIN @TABLE AS S ON T.MANDT=S.MANDT AND T.DOCNUM=S.DOCNUM

	DELETE fph.EDIDC
	FROM fph.EDIDC AS T
	JOIN @TABLE AS S ON T.MANDT=S.MANDT AND T.DOCNUM=S.DOCNUM

	DELETE fph.EDIDS
	FROM fph.EDIDS AS T
	JOIN @TABLE AS S ON T.MANDT=S.MANDT AND T.DOCNUM=S.DOCNUM

	DELETE FROM @TABLE
END

로그 날짜 2019년도 01월 01일 이전 데이터를 삭제했습니다.

 

'SAP > DataLifeCycle' 카테고리의 다른 글

SAP DELETE IDOCS ( EDIDS, EDIDC, EDID4 등)  (0) 2019.07.04
SAP 어플리케이션 로그 (BALHDR,BALDT) 삭제  (0) 2019.07.04

Leave a Comment

사SAP ERP 시스템 운영 시 사이즈가 큰 테이블 중의 하나인 BALDT는 어플리케이션 로그 테이블입니다.

해당 테이블을 주기적으로 삭제해서 일정한 사이즈를 관리할 수 있습니다.

 

데이터베이스에서 확인했을 때 현재 데이터의 건수가 약 20억건 정도 되네요.

플리케이션 로그 삭제 프로그램은 SBAL_DELETE 입니다.

만료일을 관리할 경우 "만료일이 된 로그만" 삭제하시면 되고

만료일을 관리하지 않을 경우에는 특정 기간값을 주고 삭제하시면 됩니다.

 

삭제 실행 시 수행되는 쿼리는 아래와 같습니다.

SELECT "MANDANT" AS c ,"LOGNUMBER" AS c ,"OBJECT" AS c 
,"SUBOBJECT" AS c ,"EXTNUMBER" AS c 
,"ALDATE" AS c ,"ALTIME" AS c ,"ALUSER" AS c ,"ALTCODE" AS c 
,"ALPROG" AS c ,"ALMODE" AS c ,"ALTEXT" AS c ,"USEREXITP" AS c 
,"USEREXITF" AS c ,"PROBCLASS" AS c ,"ALDATE_DEL" AS c ,"DEL_BEFORE" AS c 
,"ALSTATE" AS c ,"USEREXITT" AS c ,"ALCHDATE" AS c ,"ALCHTIME" AS c 
,"ALCHUSER" AS c ,"LOG_HANDLE" AS c ,"TABNAME" AS c ,"MSG_CNT_AL" AS c 
,"MSG_CNT_A" AS c ,"MSG_CNT_E" AS c ,"MSG_CNT_W" AS c ,"MSG_CNT_I" AS c 
,"MSG_CNT_S" AS c ,"LAST_MSGNR" AS c ,"TIM_STMP" AS c ,"DB_VERSION" AS c 
,"MSG_CNT_P1" AS c ,"MSG_CNT_P2" AS c ,"MSG_CNT_P3" AS c ,"MSG_CNT_P4" AS c 
,"CLIENT_CRE" AS c ,"CHAR_SIZE" AS c 
FROM "BALHDR" 
WHERE "MANDANT" = @P1 
AND ( "ALDATE" = @P2 AND "ALTIME" >= @P3 OR "ALDATE" > @P4 ) 
AND ( "ALDATE" = @P5 AND "ALTIME" <= @P6 OR "ALDATE" < @P7 ) 
AND ( "ALDATE_DEL" <= @P8 OR "ALDATE_DEL" = @P9 OR "DEL_BEFORE" = @P10 ) 
ORDER BY "MANDANT" ,"LOGNUMBER"  /* R3:SBAL_DELETE:707 T:BALHDR */ 
DELETE
FROM "BALDAT"
WHERE MANDANT = @P1 
AND RELID = @P2 
AND LOG_HANDLE IN ( @P3 , @P4 , @P5 , @P6 ,
@P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 ,
@P19 , @P20 , @P21 , @P22 , @P23 , @P24 , @P25 , @P26 , @P27 , @P28 , @P29 ,
@P30 , @P31 , @P32 , @P33 , @P34 , @P35 , @P36 , @P37 , @P38 , @P39 , @P40 ,
@P41 , @P42 , @P43 , @P44 , @P45 , @P46 , @P47 , @P48 , @P49 , @P50 , @P51 ,
@P52 , @P53 , @P54 , @P55 , @P56 , @P57 , @P58 , @P59 , @P60 , @P61 , @P62 ,
@P63 , @P64 , @P65 , @P66 , @P67 , @P68 , @P69 , @P70 , @P71 , @P72 , @P73 ,
@P74 , @P75 , @P76 , @P77 , @P78 , @P79 , @P80 , @P81 , @P82 , @P83 , @P84 ,
@P85 , @P86 , @P87 , @P88 , @P89 , @P90 , @P91 , @P92 , @P93 , @P94 , @P95 ,
@P96 , @P97 , @P98 , @P99 , @P100 , @P101 , @P102 )
/* R3:SAPLSBAL_DB_INTERNAL:607 T:BALDAT */

삭제 시에는 Lock이 발생할 수 있으므로 서비스 상황을

모니터링하면서 짧은 주기로 삭제하시는 것이 좋을것 같습니다.

 

'SAP > DataLifeCycle' 카테고리의 다른 글

SAP DELETE IDOCS ( EDIDS, EDIDC, EDID4 등)  (0) 2019.07.04
SAP 어플리케이션 로그 (BALHDR,BALDT) 삭제  (0) 2019.07.04

Leave a Comment

SAP ERP가 유니코드를 지원하지 않는 경우

데이터베이스에서는 한글이 깨진 데이터로 보이게 되고

이를 전송할 경우 다른 데이터베이스에서도 사용이 불가능한 경우가 생깁니다.

MAKT 테이블의 예

 

SSIS를 이용해서 데이터를 전송할 경우

- OLE DB Source 와 Destinatioin의 AlwaysUseDefaultCodePage 속성을 True로 설정합니다.

 

 

오라클에서의 인코딩은 아래와 같습니다.

인코딩 : String encoded = URLEncoder.encode(message, "Cp850");

디코딩 : strReturn = URLDecoder.decode(encoded, "EUC-KR");

 

SSMS에서 데이터를 변환해서 보기

Collation이 Korean_Wansung_CI_AS 인 서버에서 링크드 서버를 이용하거나

해당 Instance에서 DB를 복원한 후 Krean_Wansung_CI_AS 인 DB에서

SELECT CONVERT(VARCHAR, CONVERT(VARBINARY,컬럼명)) FROM 복원DB.스키마.테이블

구문을 사용하여 확인하는 방법이 있습니다.

Leave a Comment


to Top